INSERT Query problem with Quotes & Apostrophes

[Pete]

We have an application with an audit table to keep track of who changed what
and when. This is updated using the following code:
Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
cChange) VALUES ('" & CurrentUser & "', #" & Date() & "#, " & Chr(34) &
RecordChanged & Chr(34) & ")"
.CommandType = adCmdText
.Execute
End With
Set cmd1 = Nothing

The Chr(34)s were added as I ran into problem if the value of the
RecordChanged string contained an apostrophe (').

I now have a problem as a user wants to write a record containg quotes (")
and an apostrophe ('). I have written a function that strips these characters
out of the string, but ideally I would like to audit exactly what the user
has entered. I have tried playing around with adding Chr(39) into the above
CommandText but not had any luck. Help please!

 

[Douglas J. Steele]

What you have to do is decide whether you want to use single quotes or
double quotes as your delimiter, and then double each occurrence of that
delimiter symbol in your string.

Assuming you're using Access 2000 or newer, the Replace function will help
here.

In your example, you're using double quotes as the delimiter, therefore you
need to change each occurrence of " in your text to "":

.CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
cChange) VALUES ('" & CurrentUser & "', #" & Date() & "#, " & Chr(34) &
Replace(RecordChanged, Chr(34), Chr(34) & Chr(34)) & Chr(34) & ")"

Don't worry that this will impact what you're inserting. It won't: even
though you're putting two double quotes into the string, it will be
recognized as only being one for insertion purposes.

Had you wanted to use a single quote as the delimiter, you'd use:

.CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
cChange) VALUES ('" & CurrentUser & "', #" & Date() & "#, '" &
Replace(RecordChanged, "'", "''") & "')"

Exagerated for clarity, that's:

.CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
cChange) VALUES ('" & CurrentUser & "', #" & Date() & "#, ' " &
Replace(RecordChanged, " ' ", " ' ' ") & " ' )"

BTW, if there's a chance that people in other countries will be using your
application, you should be aware that putting Date() into the SQL like that
may not always work. If your user has the Short Date format set to
dd/mm/yyyy (as it is in much of the world), the SQL statement will not work
for the first 12 days of each month. My advice is to format the date
explicitly to avoid the possibility of problems:

.CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
cChange) VALUES ('" & CurrentUser & "', " & Format(Date(),
"\#mm\/dd\/yyyy\#") & ", '" & Replace(RecordChanged, "'", "''") & "')"

 

[Pete]

Thanks very much. Works a treat and I note your comment about the date (the
system will only ever be used in-hosue so this is not a problem).

Using punctuation characters in strings always seems to cause problems!
Shame there isn't a special data type "StringContainingPunctuation" or
something like that whereby Access does this for you, as the double quotes
solution is not very intuitive for novices and even trips up exerienced
programmers like myself occasionally!!

What you have to do is decide whether you want to use single quotes or
double quotes as your delimiter, and then double each occurrence of that
delimiter symbol in your string.

Assuming you're using Access 2000 or newer, the Replace function will help
here.

In your example, you're using double quotes as the delimiter, therefore you
need to change each occurrence of " in your text to "":

.CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
cChange) VALUES ('" & CurrentUser & "', #" & Date() & "#, " & Chr(34) &
Replace(RecordChanged, Chr(34), Chr(34) & Chr(34)) & Chr(34) & ")"

Don't worry that this will impact what you're inserting. It won't: even
though you're putting two double quotes into the string, it will be
recognized as only being one for insertion purposes.

Had you wanted to use a single quote as the delimiter, you'd use:

.CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
cChange) VALUES ('" & CurrentUser & "', #" & Date() & "#, '" &
Replace(RecordChanged, "'", "''") & "')"

Exagerated for clarity, that's:

.CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
cChange) VALUES ('" & CurrentUser & "', #" & Date() & "#, ' " &
Replace(RecordChanged, " ' ", " ' ' ") & " ' )"

BTW, if there's a chance that people in other countries will be using your
application, you should be aware that putting Date() into the SQL like that
may not always work. If your user has the Short Date format set to
dd/mm/yyyy (as it is in much of the world), the SQL statement will not work
for the first 12 days of each month. My advice is to format the date
explicitly to avoid the possibility of problems:

.CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
cChange) VALUES ('" & CurrentUser & "', " & Format(Date(),
"\#mm\/dd\/yyyy\#") & ", '" & Replace(RecordChanged, "'", "''") & "')"

 

[Douglas J. Steele]

I typically add a function like the following to all of my applications:

Public Function CorrectText( _
InputText As String, _
Optional RemoveNulls As Boolean = True, _
Optional Delimiter As String = "'" _
) As String
' This code was originally written by
' Doug Steele, MVP
'
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: Given a text string, this function converts the
' text so that it will work with SQL statements.
' It does this by replacing any occurrence of the
' delimiter character in the text by two occurrences
' of the delimiter character. The default delimiter
' character is a single quote ('), which will be
' replaced by two single quotes ('', not to be confused with
")
'
' Inputs: InputText String Expression
'
' Returns: InputText, with any occurrence of ' replaced by ''
' with delimiters at the beginning and end of the string.
' (i.e. Doug's will be returned as 'Doug''s')

On Error GoTo Err_CorrectText

Dim strTemp As String

strTemp = Delimiter
If RemoveNulls = True Then
strTemp = strTemp & MyReplace(MyReplace(InputText, Chr$(0), ""),
Delimiter, Delimiter & Delimiter)
Else
strTemp = strTemp & MyReplace(InputText, Delimiter, Delimiter &
Delimiter)
End If
strTemp = strTemp & Delimiter

End_CorrectText:
CorrectText = strTemp
Exit Function

Err_CorrectText:
Err.Raise Err.Number, "CorrectText", Err.Description
strTemp = vbNullString
Resume End_CorrectText

End Function

I can then simply do:

.CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
cChange) VALUES (" & CorrectText(CurrentUser) & ", #" & Date() & "#, " &
CorrectText(RecordChanged) & ")"

and not have to worry about quotes at all. You could also add a CorrectDate
function, and have:

.CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
cChange) VALUES (" & CorrectText(CurrentUser) & ", " & CorrectDate(Date())
& ", " &
CorrectText(RecordChanged) & ")"

This has an added advantage if you ever move to another platform, such as
SQL Server, where the rules regarding quotes and dates are different.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks very much. Works a treat and I note your comment about the date (the
system will only ever be used in-hosue so this is not a problem).

Using punctuation characters in strings always seems to cause problems!
Shame there isn't a special data type "StringContainingPunctuation" or
something like that whereby Access does this for you, as the double quotes
solution is not very intuitive for novices and even trips up exerienced
programmers like myself occasionally!!

 

[Pete]

Thanks for this. Is your MyReplace function significantly different from the
standard replace function?

I was interested about your comment concerning SQL Server. I am about to
rewrite another Access 2003 application with the intention that it will be
SQL Server compatible so that, if we want to, we can move from an Access
backend to SQL Server Express when it is released. I don't know whether it is
possible to write a front end that is mutually compatible with both. I have
had a small amount of experience with SQL Server and not had too many
problems. I note that there is an option to make queries ANSI 92 compatible
in Access and I am aware of the differences with yes/no fields and text
fields.

 

[Tim Ferguson]

Thanks very much. Works a treat and I note your comment about the date
(the system will only ever be used in-hosue so this is not a problem).

.... until the new Canadian temp resets her Regional Settings ...


Tim F

 

[Douglas J. Steele]

Sorry: I should have read my code closer! It was from an Access 97 database,
and Access 97 doesn't have the Replace function in it, so I had to write my
own. Change MyReplace to Replace everywhere for Access 2000 and newer.

I've got front-ends that go against both Jet and SQL Server databases. You
just have to be careful that you use the correct delimiters in your queries,
so I use a slightly different version of CorrectDate, where I pass not only
the date field to format, but also a flag as to whether it's for Jet or SQL
Server. I've got a sample in my September 2003 Smart Access column
(downloadable at
http://members.rogers.com/douglas.j.steele/SmartAccess.html)

 

[Michael Koenig]

Help me, Douglas Steele... you are my only hope....

Hi Doug;

You helped me a week ago, and once again, I find myself turning to you...

A little background - I'm an Access 2000 baby, and I've been hired into an
organization that runs Access 97 exclusively. (CRINGE!) I've been tasked with
analyzing over 140 Access 97 databases and documenting them fully for the
purpose of data consolidation. As there are so many, I've elected to develop
an Access 97 database for automating the analysis process, and for storing
the results for later cross-referencing.

When running some of the databases through my analysis tool, I'm running
into some BIZARRE object names that I could readily accommodate using the
Replace function within Access 2000. But, as you are well aware, this
function isn't available in Access 97. In reading this post for guidance,
inspiration, et cetera, I saw your reference to having written your own
Replace-style function, called MyReplace.

Is there any chance that you'd be willing to share? If not, could you
perhaps provide me with a few tips as to what I would need to code for when
attempting to write my own?

I'd appreciate any and all help I can get on this...

Michael

 

[John Spencer]

John Viescas posted these a long time back. Watch out for line wraps

'Courtesy of John Viescas

Public Function InStrRev(strCheck As Variant, _
strMatch As Variant, _
Optional intStart As Integer = -1, _
Optional intCompare As Integer = 2) As Variant
'-----------------------------------------------------------
' Inputs: String to check,
' match string,
' optional starting position (default = -1),
' optional string compare value (default vbDatabaseCompare)
' Outputs: Position of match string, starting from the end
' Created By: JLV 11/15/01
' Last Revised: JLV 11/15/01
' ** Duplicates the functionality of the VB 6 INSTRREV function.
'-----------------------------------------------------------
Dim intS As Integer, intR As Integer
Dim intI As Integer, intLenC As Integer, intLenM As Integer

' Do some initial checks
If (intCompare < 0) Or (intCompare > 2) Then
Err.Raise 5
Exit Function
End If
If IsNull(strCheck) Then
InStrRev = Null
Exit Function
End If
If VarType(strCheck) <> vbString Then
Err.Raise 5
Exit Function
End If
If IsNull(strMatch) Then
InStrRev = Null
Exit Function
End If
If VarType(strMatch) <> vbString Then
Err.Raise 5
Exit Function
End If
If Len(strCheck) = 0 Then
InStrRev = 0
Exit Function
End If
If Len(strMatch) = 0 Then
InStrRev = intStart
Exit Function
End If
If intStart > Len(strMatch) Then
InStrRev = 0
Exit Function
End If
If Len(strMatch) > Len(strCheck) Then
InStrRev = 0
Exit Function
End If

' OK, have some work to do!
intS = intStart
intLenC = Len(strCheck)
intLenM = Len(strMatch)
If intS = -1 Then intS = intLenC
' Set default not found
InStrRev = 0
' Now loop to see if we can find it
For intI = intS To 1 Step -1
intR = InStr(intI, strCheck, strMatch, intCompare)
If intR <> 0 Then
InStrRev = intR
Exit For
End If
Next intI

End Function


Public Function Join(varArray As Variant, _
Optional strDelimiter As String = "") As String
'-----------------------------------------------------------
' Inputs: An array of strings and an optional delimiter
' Outputs: A concatenated string assembled from the
' array elements, delimited by the optional
' delimiter character
' Created By: JLV 09/05/01
' Last Revised: 09/05/01
' ** Duplicates the functionality of the VB 6 JOIN function
'-----------------------------------------------------------
Dim intL As Integer, intU As Integer, intI As Integer
Dim strWork As String

If Not IsArray(varArray) Then Exit Function
intL = LBound(varArray)
intU = UBound(varArray)
strWork = varArray(intL)
For intI = intL + 1 To intU
strWork = strWork & strDelimiter & varArray(intI)
Next intI
Join = strWork

End Function

Public Function Replace(strIn As Variant, strFind As String, _
strReplace As String, Optional intStart As Integer = 1, _
Optional intCount As Integer = -1, _
Optional intCompare As Integer = 0) As String
'-----------------------------------------------------------
' Inputs: String to search and replace,
' search string, replacement string,
' optional starting position (default = 1),
' optional replacement limit (default = -1 .. ALL)
' optional string compare value (default = 0 .. vbBinaryCompare)
' Outputs: Replaced string
' Created By: JLV 09/05/01
' Last Revised: JLV 09/05/01
' ** Duplicates the functionality of the VB 6 REPLACE function.
'-----------------------------------------------------------
Dim strWork As String, intS As Integer, intCnt As Integer
Dim intI As Integer, intLenF As Integer, intLenR As Integer

If (intCompare < 0) Or (intCompare > 2) Then
Err.Raise 5
Exit Function
End If
If VarType(strIn) <> vbString Then
Err.Raise 5
Exit Function
End If
strWork = strIn
intS = intStart
intCnt = intCount
intLenF = Len(strFind)
intLenR = Len(strReplace)
' If find string zero length or count is zero, then nothing to replace
If (intLenF = 0) Or (intCnt = 0) Then
Replace = strIn
Exit Function
End If
' If start beyond length of string, return empty string
If intS > Len(strWork) Then
Replace = ""
Exit Function
End If

' Got some work to do -- find strings to replace
Do
intI = InStr(intS, strWork, strFind, intCompare)
If intI = 0 Then Exit Do
' Insert the replace string
strWork = Left(strWork, intI - 1) & strReplace & _
Mid(strWork, intI + intLenF)
intS = intS + intI + intLenR - 1 ' Bump start to end of the replace
string
intCnt = intCnt - 1 ' Decrement the max replace
counter
Loop Until intCnt = 0
Replace = strWork

End Function

Public Function Split(strToSplit As String, _
Optional strDelimiter As String = " ", _
Optional intCount As Integer = -1, _
Optional intCompare As Integer = 0) As Variant
'-----------------------------------------------------------
' Inputs: String to search,
' delimiter string,
' optional replacement limit (default = -1 .. ALL)
' optional string compare value (default vbBinaryCompare)
' Outputs: Array containing items found in the string
' based on the delimiter provided
' Created By: JLV 09/05/01
' Last Revised: JLV 09/05/01
' ** Duplicates the functionality of the VB 6 SPLIT function.
'-----------------------------------------------------------
Dim strWork As String, intCnt As Integer, intIndex As Integer
Dim intI As Integer, strArray() As String

If (intCompare < 0) Or (intCompare > 2) Then
Err.Raise 5
Exit Function
End If
strWork = strToSplit
intCnt = intCount
' If count is zero, return the empty array
If intCnt = 0 Then
Split = strArray
Exit Function
End If
' If the Delimiter is zero-length, return a 1-entry array
If strDelimiter = "" Then
ReDim strArray(0)
strArray(0) = strWork
Split = strArray
Exit Function
End If

' Decrement count by 1 because function returns
' whatever is left at the end
intCnt = intCnt - 1
' Loop until the counter is zero
Do Until intCnt = 0
intI = InStr(1, strWork, strDelimiter, intCompare)
' If delimiter not found, end the loop
If intI = 0 Then Exit Do
' Add 1 to the number returned
intIndex = intIndex + 1
' Expand the array
ReDim Preserve strArray(0 To intIndex - 1)
' Use index - 1 .. zero-based array
strArray(intIndex - 1) = Left(strWork, intI - 1)
' Remove the found entry
strWork = Mid(strWork, intI + 1)
intCnt = intCnt - 1
Loop
' Put anything left over in the last entry of the array
If Len(strWork) > 0 Then
intIndex = intIndex + 1
ReDim Preserve strArray(0 To intIndex - 1)
strArray(intIndex - 1) = strWork
End If

' Return the result
Split = strArray

End Function

 

[Michael Koenig]

John,

You are my Hero of the Week!! Thank you for this!