Syntax Error

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

I'm using the following VBA code to lookup a name in a
dynaset:

rec.FindFirst "Name = '" & Me.PatientName & "'"

Normally, it works fine. However, as expected, if the
patientname value includes one or more single quotation
marks, e.g., John D'Agostino, I receive a syntax error.
Any suggestions? Thanks.

Ken
 
Hi Ken

You can either:
a) use double quotes instead of single:
rec.FindFirst "Name =""" & Me.PatientName & """"
(Note that in VBA, you must use two consecutive double-quotes in a
literal string to represent one double-quote)
or:
b) double every single-quote in your string:
rec.FindFirst "Name = '" & Replace(Me.PatientName, "'", "''") & "'"
(SQL is like VBA and interprets two consecutive quotes as part of the
string)

Of course, (a) will fail if the match string contains a double-quote, so (b)
is probably the better option.
 
Graham,

Thanks! I went with option B and it works. You're genius!

Ken
-----Original Message-----
Hi Ken

You can either:
a) use double quotes instead of single:
rec.FindFirst "Name =""" & Me.PatientName & """"
(Note that in VBA, you must use two consecutive double-quotes in a
literal string to represent one double-quote)
or:
b) double every single-quote in your string:
rec.FindFirst "Name = '" & Replace
Click to expand...
(Me.PatientName, "'", "''") & "'"
(SQL is like VBA and interprets two consecutive quotes as part of the
string)

Of course, (a) will fail if the match string contains a double-quote, so (b)
is probably the better option.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


I'm using the following VBA code to lookup a name in a
dynaset:

rec.FindFirst "Name = '" & Me.PatientName & "'"

Normally, it works fine. However, as expected, if the
patientname value includes one or more single quotation
marks, e.g., John D'Agostino, I receive a syntax error.
Any suggestions? Thanks.

Ken
Click to expand...


.
Click to expand...
 
Back
Top