aspostrophe in SQL

[DentTec]

My SQL statement was working fine except when the value of criteria has the aspostrophe in it. How do you handle this problem ? Thanks

 

[Jeanette Cunningham]

Post the sql.
Here is an explanation of how to handle apostrophes in sql.
http://allenbrowne.com/casu-17.html

 

[DentTec]

Thanks for the link Jeanette, I'll check it out soon.

Sorry I posted a wrong one. Here is the correct one

Dim sql As String
sql = "SELECT blah blah, [Outstanding Invoices].Client"
sql = sql & " FROM [Outstanding Invoices]"
sql = sql & " GROUP BY blah"
sql = sql & " HAVING ((([Outstanding Invoices].Client) = '" & clientSelected & "'))"
sql = sql & xyz;"

Me.RecordSource = sql

whereas clientSelected is a value selected from a list box in my form

I got an erro when my Client is OTTO'S.

 

[Galaxiom]

It does depend on the circumstances bu double quotes and also cause grief.

One way is to double up the single quotes.

Replace({expression},"'", "''")

The same can be done with double quotes.

The most reliable way is if the string can be taken directly from a control.

This common usage will fail if an apostrophe is included

DMax("fieldname", "tablename", "stringfield='" & Me.somecontrol & "'")

But the following will be fine for the same string because Access deals with the datatype automatically and does not require the string delimiters.

DMax("fieldname", "tablename", "stringfield=Forms!formname.somecontrol")

 

[Galaxiom]

[Outstanding Invoices].Client<>"OTTO'S"

Access is expecting the second single quote to close what it interprets as open single quote.

However you woudn't have a problem if you were to use
[Outstanding Invoices].Client<>Forms!formname.controlname

 

[DentTec]

GalaxiomAtHome: I just correct my code, I posted a wrong SQL earlier. I'll take a look at your suggestion tomorrow. Thanks

 

[Jeanette Cunningham]

Dim sql As String
sql = "SELECT blah blah, [Outstanding Invoices].Client"
sql = sql & " FROM [Outstanding Invoices]"
sql = sql & " GROUP BY blah"
sql = sql & " HAVING ((([Outstanding Invoices].Client) = """ & clientSelected & """))"
sql = sql & xyz;"

 

[DentTec]

Thanks Jeanette, it works great.