DLookup with variable that has single quote 1

[zevw]

Hi!

I am asking the user if they want to save a description for later use, so that next time they do not need to retype the whole thing just find it in the drop down combo box.

I will only save the description after making a DLookup to see if this description exists in the the table already. It works fine until a description has a single quote in it, I will get an error, any ideas how to escape the single quote and still be able to make the DLookup.

This is the code

If Nz(DLookup("Description", "Descriptions", "Description = '" & Me.Description & "'"), "") = "" Then

Thanks in advancd!!

 

[GingerR]

you could use REPLACE function to replace the any quotes in the existing description with say a @ or a > or whatever, and do the same with Me.Description. Of course that could cause problems too, but if you pick a crazy enough character(or string of crazy characters) no one will ever use it. Hopefully

 

[HitechUser]

zevw,

You can use two double quotation marks to replace one single quotation mark. See DLookUp link below

http://support.microsoft.com/default.aspx?scid=kb;en-us;208786

I hope this helps..

 

[PHV]

Already answered several times:
If Nz(DLookup("Description", "Descriptions", "Description = '" & [tt]Replace(Me.Description, "'", "''")[/tt] & "'"), "") = "" Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[zevw]

Thanks PHV!