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]

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.

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, "'", "''") & "'"
...