Delimiter problems

[Les Isaacs]

Hi All
I have the recordset assignment below which works fine until it hits an employee with an apostrophe in it - it then hangs. I've tried doubling up on the quotes, and/or on the apostrophes, and I've even tried doing a findandreplace to get rid of any apostrophes in employee: but all to no avail. If anyone can help I'd be extremely grateful.
Many thanks
Les


Set rsEmployees = CurrentDb.OpenRecordset("SELECT staff_name,[hourly rate],[type name],[employment start]" & _
" FROM staffs" & _
" WHERE staff_name = '" & employee & "';")

 

[jdraw]

Try (untested)
Set rsEmployees = CurrentDb.OpenRecordset("SELECT Replace(staff_name,"'","''"),[hourly rate],[type name],[employment start]" & _
" FROM staffs" & _
" WHERE staff_name = '" & employee & "';")

You needed to double up on the quote, as I recall.

Is the quote in the staffs table or iiin variable Employee?
There are several representations of quote also '"` and more.

 

[Les Isaacs]

Hi Jdraw
Thanks for your reply. Unfortunately I still get the same error with your suggested amendment. In fact I had already tried something similar, and then wondered whether I needed to do the replace on the WHERE clause as well, so I had:

Set rsEmployees = CurrentDb.OpenRecordset("SELECT FindAndReplace(staff_name,"'","''"),[hourly rate],[type name],[employment start]" & _
" FROM staffs" & _
" WHERE FindAndReplace(staff_name,"'","''") = '" & FindAndReplace(employee,"'","''") & "';")

This didn't actually hang, but didn't work because it didn't limit the resulting recordset by matching staff_name to employee - it returned every record from the staffs table (or at least it would have done, except that the FindAndReplace was taking forever on all the records - about 500,000 of them!!).

Does this give you any clues - 'cos I'm stumped!
Thanks again
Les

 

[Beetle]

In your original SQL, just replace each single quote with two double quotes;


Set rsEmployees = CurrentDb.OpenRecordset("SELECT staff_name,[hourly rate],[type name],[employment start]" & _
" FROM staffs" & _
" WHERE staff_name = """ & employee & """;")

 

[Les Isaacs]

Beetle
Worked a treat - I'm so grateful - very many thanks!!
Les

 

[jdraw]

Thanks for sharing.