• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Aprostrophe

Aprostrophe

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.

Inputs
sFieldString

Assumes
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


Rate Aprostrophe

• 1
• 2
• 3
• 4
• 5

(5(5 Vote))

'***********************************************************************
' 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