Have you ever try so send a string variable to MS Access that have
apostrophes using a SQL Statement? If YES you will get a run time ERROR
Here is your solution….A function that formats the
variable before sending it to the database.
Original Author: Gaetan Savoie
Inputs
sFieldString
Assumptions
This code should be used in your Classes.
For example :
let say myVar=” Gaetan’s”
the follwing statement will give you errors:
SSQL=”INSERT INTO tablename (FirstName) VALUES (” & chr(39) & myvar & chr(39) & “)”
To fix it do the following:
myVar=apostrophe(myvar)
SSQL=”INSERT INTO tablename (FirstName) VALUES (” & chr(39) & myvar & chr(39) & “)”
Returns
Aphostrophe
API Declarations
None
Code
'***********************************************************************
' Function: Apostrophe
' Argument: sFieldString
' Description: This subroutine will fill format the field we
' want to store in the database if there is some apostrophes
' in the field.
'***********************************************************************
Public Function Apostrophe(sFieldString As String) As String
If InStr(sFieldString, "'") Then
Dim iLen As Integer
Dim ii As Integer
Dim apostr As Integer
iLen = Len(sFieldString)
ii = 1
Do While ii <= iLen
If Mid(sFieldString, ii, 1) = "'" Then
apostr = ii
sFieldString = Left(sFieldString, apostr) & "'" & _
Right(sFieldString, iLen - apostr)
iLen = Len(sFieldString)
ii = ii + 1
End If
ii = ii + 1
Loop
End If
Apostrophe = sFieldString
End Function