Problem with apostrophe

[kite54surfer]

Hi all,

I apologize if not posting correctly, my first time.
I am having problem with the field "Scheduled" (7901-D'Acunha-A-5) getting:
" Syntax error (missing operator) in query expression "7901-D'Acunha-A-5', " , 'Done', 'Update', #4/6/2009 2:21:17 PM#)'

I know is related to the apostrophe and I searched and found results on (single quotes), I tried to make changes myself, with no luck.

I wasn't sure how much I should post.
==========================================

Function build_sql(Schedule_ID As Date, operation As String) As String
     build_sql = "Insert Into tblJobScheduleAuditLog ([ScheduleID],[Scheduled], [JobDate], [Status],[Action],[Timestamp]) Values (#"
     build_sql = build_sql & ScheduleID & "# , "
     build_sql = build_sql & "'" & _
DLookup("Scheduled", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
     build_sql = build_sql & "'" & _
DLookup("JobDate", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
      build_sql = build_sql & "'" & _
DLookup("Status", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
build_sql = build_sql & "'" & operation & "', "
     build_sql = build_sql & "#" & Now() & "#)"
   End Function

===========================================

Thanks for any help in advance,
Emilio

[ChipR]

You're right, you won't be able to surround a text field that contains apostrophes with apostrophes. The solution is to surround the data with quotes instead. Try:

build_sql = build_sql & """" & DLookup(...) & """"

The double " gets turned into a single " in the resulting string.

[kite54surfer]

@ChipR
Thank you very much!

Great fast help

Emilio

[NeoPa]

An alternative (that is consistent with the SQL standards) is to provide a function to double up any single quotes (not actually apostrophes, but often treated the same) within your data.

In VBA it's fairly straightforward, as the Replace() function is already available.

...
Build_SQL = Build_SQL & "'" & Replace(YourValue, "'", "''") & "'"
...