Help with ' in data and SQL Statement (1 Viewer)

[neilhoop]

I have the following SQL statement under development.

Dim ssql As String

ssql = "INSERT INTO tblFC (CertNum, Work, Pipework, EMCV, Tightness, Bonding, DDate, NumAppl,"

ssql = ssql & "ClientID, FName, Add1, Add2, Add3, City, County, Post, Email, TelHome, TelMob,"

ssql = ssql & "TelOther) "

ssql = ssql & "VALUES ("

ssql = ssql & Forms![certificates]![ID]

ssql = ssql & ",'" & Forms![certificates]![Work] & "'"

If Forms![certificates]![Pipework] = "-1" Then
ssql = ssql & ",'Yes'"
Else
ssql = ssql & ",'No'"
End If

If Forms![certificates]![EMCV] = "-1" Then
ssql = ssql & ",'Yes'"
Else
ssql = ssql & ",'No'"
End If

If Forms![certificates]![Tightness] = "-1" Then
ssql = ssql & ",'Yes'"
Else
ssql = ssql & ",'No'"
End If

If Forms![certificates]![Bonding] = "-1" Then
ssql = ssql & ",'Yes'"
Else
ssql = ssql & ",'No'"
End If

ssql = ssql & ",#" & Forms![certificates]![DDate] & "#"

ssql = ssql & ",'" & Forms![certificates]![Numappl] & "'"

ssql = ssql & ",'" & Forms![certificates]![subClient].Form![Client ID] & "'"

ssql = ssql & ",'" & Forms![certificates]![subClient].Form![FName] & "'"

ssql = ssql & ",'" & Forms![certificates]![subClient].Form![Add1] & "'"

ssql = ssql & ",'" & Forms![certificates]![subClient].Form![Add2] & "'"

ssql = ssql & ",'" & Forms![certificates]![subClient].Form![Add3] & "'"

ssql = ssql & ",'" & Forms![certificates]![subClient].Form![City] & "'"

ssql = ssql & ",'" & Forms![certificates]![subClient].Form![County] & "'"

ssql = ssql & ",'" & Forms![certificates]![subClient].Form![Post] & "'"

ssql = ssql & ",'" & Forms![certificates]![subClient].Form! & "'"

ssql = ssql & ",'" & Forms![certificates]![subClient].Form![tel home] & "'"

ssql = ssql & ",'" & Forms![certificates]![subClient].Form![Tel Mob] & "'"

ssql = ssql & ",'" & Forms![certificates]![subClient].Form![Tel Other] & "'"

ssql = ssql & ");"


It's working fine until the data in Forms![certificates]![Work] contains a '

At this point I get a run time error, How can I get around this?

the appropriate line in my query is

ssql = ssql & ",'" & Forms![certificates]![Work] & "'"

and is located near the top of the statements.

----------
Neil

 

[petehilljnr]

You could try using double quotation marks (") instead:

e.g. ssql = ssql & "," & chr(34) & ....... & chr(34)

Pete

 

[neilhoop]

Pete,

Thanks for that, works a treat.

----------
Neil

 

[Rameez]

what's chr(34)...even i have the same prob...

 

[DCrake]

Chr(34) is the double quotation character expressed as a number.

 

[Rameez]

Thanks DcRake

 

[neilhoop]

I found that the addition of Chr(34) will work as long as the field value is not empty or has a " in it.

The code below will work if the field is empty, has a ' or a " in it.

It will also work if there are more than one ' or " in the data.

It will NOT work if the data contains any combination of ' and ". This would have to be tested when the data is entered and the user made to correct it accordingly.

Dim stpos As Integer


If IsNull(field) = True Then ' Is the field empty?

ssql = ssql & ",'" & field & "'" ' Yes field is empty so add ''

Else ' Field isn't empty

stpos = InStr(1, field, "'", vbTextCompare) ' Check for an apostrophe (') in the field data

If stpos <> 0 Then ' If an ' is found do this

ssql = ssql & "," & Chr(34) & field & Chr(34) ' add " to each end of data

Else ' An ' is not found

ssql = ssql & ",'" & field & "'" ' so add ' to each end of data

End If

End If