Excel VBA, SQl and apostrophies

--amadeus--

Hi,

I have an Access database which uses Excel as it's front end and user interface. The main input / output is managed through SQL created using VBA (using variables as parameters). It's 99% complete and teh client os very happy but I have an issue with some of teh reporting.

Basically I need to create reports based on staff relationships, so I need to populate a drop down with staff who report into a selected member of staff. My challenge is that several of teh staff have apostrophies in thier names - Paddy O'Brien or John O'Conner for example.

The relevent code snippet is:

Set Db = Workspaces(0).OpenDatabase(Path, False, False, "MS Access;PWD=[I]mypass[/I]")
         
            strsql = "SELECT distinct names.business_unit_leader FROM names where names.department_manager='" & vardeptman & "' and names.business_unit_leader <>'N/A'"
        
       Set Rs = Db.OpenRecordset(strsql)
            Ws.Range("r49").CopyFromRecordset Rs

vardeptman is a variable that is populated with the Dept Manager's name. In most cases this is very simple and works well, it sets the database, populates the strsql string variable with the SQL and creates the recordset. This is then copied into the sheet (cell R49)

However when the name has an apostrophie the SQL statement is corrupted as apostrophies are used to delimit a text string for teh parameter values. I can't use quotes ("") as they delimit the sql string itself.

Has anyone got any solutions to this?

Thanks!

seamus

If Access SQL is using the same SQL implementation as MSSQL, then single quotes are delimited by another single quote (ridiculous I know).

So in order to escape a name for your SQL, you need to replace a single quote with two single quotes (not a double quote).

This means that your statement becomes

strsql = "SELECT distinct names.business_unit_leader FROM names where names.department_manager='" & Replace(vardeptman, "'", "''") & "' and names.business_unit_leader <>'N/A'"

This will generate a SQL statement like

SELECT distinct names.business_unit_leader FROM names where names.department_manager='John O''Brien' and names.business_unit_leader <>'N/A'

--amadeus--

Works like a charm, you're a genius!

Thanks

seamus

FWIW, this is probably the one thing that catches me out most frequently.

I just spent half of today trying to troubleshoot a problem where a javascript function was randomly not working on some people's machines.

It was only ten minutes ago when someone sent me a list of the names of the people for whom it didn't work, that the penny dropped. The javascript function used the visitor's name, which I didn't escape. D'oh.

amen

not trying to rain on yoru parade but you shouldn't be using a persons name like this. If you change the spelling of the name (or lady gets married and has a different surname) you would have to change all of your references.

You should really have a user table with a username and userid and then use the userid to do the staff assingments.

That way you can change the name in one place and everythgin works.

In general natural keys i.e. real names, date of birth, address etc should not be used as keys.

Google normalisation