How to handle an apostrophe inside an SQL statement? 3

[steve728]

The following .Fields("Description").Value has several apostrophes and # symbols that must remain. How do I change my syntax so that I can insert these contents into
the destination field???

CurrentProject.Connection.Execute "INSERT INTO tblName (RequestNumber,[description]) Values (" & .Fields("RequestNumber").Value & ", '" & .Fields("Description").Value & "', '" & .Fields("RequestStatus").Value & "', '" _
)", , 129

Steve728

 

[PHV]

Replace this:
& .Fields("Description").Value &
with this:

& Replace(.Fields("Description").Value, "'", "''") &

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[steve728]

This is the actual SQL statement that I am trying to work with. I made the change below but still get the same syntax run-time error. Can you tell me where I am still going wrong?

CurrentProject.Connection.Execute "INSERT INTO tblISRProblems (RequestNumber,[Type],RequestedBy," _
& "RequestDate,[area],[description],[status],AffectedGroup,[Comments],DateClosed) Values " _
& "( " & .Fields("RequestNumber").Value & ", '" & strThisType & "', '" & .Fields("RequestedBy").Value _
& "', #" & .Fields("RequestDate").Value & "#, '" & .Fields("AffectedGroup").Value & "', " _
& Replace(.Fields("Description").Value, "'", "''") & ", '" & .Fields("RequestStatus").Value & "', '" _
& .Fields("AffectedGroup").Value & "', '" & .Fields("Comments").Value _
& "', #" & .Fields("DateClosed").Value & "#)", , 129

Steve728

 

[PHV]

You probably have to do the same sanity checking for "Comments".
Anyway, you forgot to enclose the description value with single quotes:
& [!]"'" & [/!]Replace(.Fields("Description").Value, "'", "''") & "[!]'[/!], '" & .Fields("RequestStatus").Value & "', '" _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vacant]

Replace? that's a new one to me, thanks for that

my solution would have been:

CurrentProject.Connection.Execute "INSERT INTO tblName (RequestNumber,[description]) Values (" & .Fields("RequestNumber").Value & ", " & """"[/color red] & .Fields("Description").Value & """"[/color red] & ", '" & .Fields("RequestStatus").Value & "', '" _
)", , 129

 

[PHV]

Vacant, your suggestion will fail if the Description contains double-quote too ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vacant]

I'm learning more by the minute! Thanks again