APOSTROPHE BAD, PLEASE HELP ASAP!

[JasonGS]

Using VB6/ADO/Access MDB file...

I have a table that contains a field that [in some cases] will contain an apostrophe ' and when I try to query for the name O'BRIEN for instance with the following code...

adoRset.Open "SELECT * FROM table WHERE lastname = '" & txtLastName.Text & "' ORDER BY lastname", adoConn

I get an error of Syntax error (missing operator) in query expression 'employee LIKE '%O'BRIEN%''.... any ideas how to get around this problem?

[Mongo]

See the MSDN Article at:

http://support.microsoft.com/support/kb/articles/Q178/0/70.asp

[JHausmann]

You could scan your string for apostrophe's and, if you find one, use an alternative statement.

adoRset.Open "SELECT * FROM table WHERE lastname = " & """" & txtLastName.Text & """" & " ORDER BY lastname", adoConn

is equivalent to what you're using. Of course, if there are any names with quotes in them, you'll be hosed... :)

[Clunietp]

All you have to do is replace the apostrophies with 2 apostrophies (not a quote) (ex '='')

just use the VB6 REPLACE function


adoRset.Open "SELECT * FROM table WHERE lastname = '" & replace(txtLastName.Text, "'", "''") & "' ORDER BY lastname", adoConn


it works every time :)

[JHausmann]

Well, between Tom's answer and mine, there's no need to go to the Microsoft URL...

:) :) ;) :) :)

[JasonGS]

Thank you all for your help. Go figure why 'O''Brien' fixed the problem, but who am I to critique it?!?

Thanks again!