dlookup to handle both " and '

[Andrew Brill]

say I have a dlookup as follows (I've put incorrect spaces around the quotes
so you can read it!):

dlookup("whatever","whereever","field = ' " & userinput & " ' ")
this works if the user enters " in their input but not '

now I can change this to
dlookup("whatever","whereever","field = "" " & userinput & " "" ")
and the user can then enter ' but not "

so... how do I write it so they can enter either?

Thanks,
Andrew

 

[Stefan Hoffmann]

hi Andrew,

dlookup("whatever","whereever","field = ' " & userinput & " ' ")
this works if the user enters " in their input but not '
now I can change this to
dlookup("whatever","whereever","field = "" " & userinput & " "" ")
and the user can then enter ' but not "
so... how do I write it so they can enter either?

Escape always the single quote:

DLookup("", "", "field='" & Replace(input, "'", "''") & "'")

Replace is available >= A2K.

mfG
--> stefan <--

 

[Douglas J. Steele]

You're going to have to use the Replace function on their input to change
their input to either have two single quotes in a row, or two double quotes
in a row, depending on what you're using as a delimiter (if you're
delimiting with a single quote, you need to double up any of the single
quotes they input: if you're delimiting with a double quote, you need to
double up any of the double quotes they input):

DLookup("whatever","whereever","field = '" & Replace(userinput, "'", "''") &
"'")

or

DLookup("whatever","whereever","field = """ & Replace(userinput, """",
"""""") & """")

Exagerated for clarity, those are:

DLookup("whatever","whereever","field = ' " & Replace(userinput, " ' ", " '
' ") & " ' ")

or

DLookup("whatever","whereever","field = " " " & Replace(userinput, " " " ",
" " " " " ") & " " " ")

These could also be rewritten as:

DLookup("whatever","whereever","field = " & Chr$(39) & Replace(userinput,
Chr$(39), Chr$(39 & Chr$(39)) & Chr$(39))

or

DLookup("whatever","whereever","field = " & Chr$(34) & Replace(userinput,
Chr$(34), Chr$(34 & Chr$(34)) & Chr$(34))


For more information, check my May, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Andrew Brill]

Thanks for the help guys, much appreciated!