Single-quotes in lookup field

[ChrisGreaves]

I am having trouble getting my brain to work on this.

A company name such as Cathy's Crawly Composters contains an isolated single-quote character.

I have experimented with removing all single-quotes from a company name to generate a satisfactory key, but then I have to remove all single quotes from all company names whenever I want to lookup the table using CompanyName as a key.

Code: Select all

            strClientBusiness = rstCLIENT.Fields("Business")
            strClientBusiness = strReplaceAll(strClientBusiness, "'", "")
            Set rstCOMPANY = dbs.OpenRecordset("select * from Company where Business = '" & strClientBusiness & "'")

I suspect that my real problem lies in building the WHERE clause in the SQL-like statement.

The business name might contain any character nowadays. Gone are the days when only a single-quote (O'Leary, O'Clogs etc.) marred the pristine letters-only format.

This means that either one strips all non-alpha characters on the fly for every type of query, or else one has to be very crafty in crating a WHERE cluse.

Right?

In the case of single-quotes it seems to me that I would end up:
(1) Replacing all single-quotes with doubled single-quotes
(2) Wrapping all of that in doubled double-quotes to be able to embed the lot in a VBA character string.

Or have I missed something here?

[HansV]

If you enclose the string value in double-quotes, you don't have to do anything with single quotes:

strClientBusiness = rstCLIENT.Fields("Business")
Set rstCOMPANY = dbs.OpenRecordset("select * from Company where Business = " & Chr(34) & strClientBusiness & Chr(34))

If there is any chance that the company name contains a double-quotes, you'd have to double those:

strClientBusiness = rstCLIENT.Fields("Business")
strClientBusiness = strReplaceAll(strClientBusiness, Chr(34), Chr(34) & Chr(34))
Set rstCOMPANY = dbs.OpenRecordset("select * from Company where Business = " & Chr(34) & strClientBusiness & Chr(34))

[ChrisGreaves]

If you enclose the string value in double-quotes, you don't have to do anything with single quotes:

So instead of replacing single-quotes in the string because I'm using single-quotes to define the string in the WHERE clause, I should use double-quotes in the WHERE clause.
That makes sense.
Unless, as you say, I have one or more double-quotes in the string, in which case I have merely flipped the original problem between single- and double-quote players.

If I knew for certain that I'd never have a double-quote in a string, then the solution to embrace using double-quotes works fine.

Part of my premise is that one never knows what might be coming, especially in terms of business names.

I need therefore to inspect the string at least for single- and double-quotes and take the appropriate action as you outlined above.
I need to inspect for any other character that might upset the WHERE clause; possibly none, but that means that I always need to inspect for both single- and for double-quotes?

[StuartR]

...I need therefore to inspect the string...

I am reminded of the story about the school head teacher who wrote a letter to a parent asking why they had named their child
delete * from student_names

[HansV]

I need to inspect for any other character that might upset the WHERE clause; possibly none, but that means that I always need to inspect for both single- and for double-quotes?

I don't think you need to check for other characters. The code from my previous reply should work OK with strings containing single-quotes, double-quotes or both: the code doesn't use single-quotes so they are treated 'as is', and it handles double-quotes by doubling them.