Problems with quotes in string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I use the following code to retrieve a course #:
Session = DLookup("[SessionID]", "schSESSION", "[SessionName]='" & cmbSessions.Text & "'")

The problem is that sometimes the Session Name includes quotes - which then gives me an error since it thinks the statement is incomplete. The message I get is "Runtime Error 3075: Syntax Error (missing operator) in query expression '[SessionName]='Early Childhood Educators' Conference". This happens anytime a course has an apostrophe in the name. Any suggestions on how to get this to work?


Thanks in advance,

Eric Chajmovic
 
Eric

Double up on the apostrophe's in the string. I use a public function to
massage the string before Dlookup sees it.

Public Function FixQuotes(strQuoted As String) As String
' Purpose Double up single quotes for use in a Sql Update query
Dim i As Integer, strOut As String

strOut = ""
For i = 1 To Len(strQuoted)
If Mid(strQuoted, i, 1) = Chr(39) Then
strOut = strOut & Chr(39)
End If
strOut = strOut & Mid(strQuoted, i, 1)
Next
FixQuotes = strOut
End Function

The above function will work for all versions of access. If you are access
2K and higher you can use the built in Replace() finction to do the same
thing with a lot less code.

Public Function FixQuotes(strQuoted As String) As String
' Purpose Double up single quotes for use in a Sql Update query
FixQuotes = replace(strQuoted, "'", "''")
End Function

Then your Dlookup would be written as:

Session = DLookup("[SessionID]", "schSESSION", "[SessionName]='" &
FixQuotes(cmbSessions.Text) & "'")

Ron W

echajmovic said:
Hello,

I use the following code to retrieve a course #:
Session = DLookup("[SessionID]", "schSESSION", "[SessionName]='" & cmbSessions.Text & "'")

The problem is that sometimes the Session Name includes quotes - which
Click to expand...
then gives me an error since it thinks the statement is incomplete. The
message I get is "Runtime Error 3075: Syntax Error (missing operator) in
query expression '[SessionName]='Early Childhood Educators' Conference".
This happens anytime a course has an apostrophe in the name. Any
suggestions on how to get this to work?
 
Back
Top