Celtic name issue

[Malcy]

Hi
I have a DLookup on a form to identify whether a person has been already entered. The code is

    varX = DLookup("lngConId", "tblContacts", "[strConSname] = '" & Me.txtSname & "' And [strConFname] = '" & Me.txtFname & "' And [strConAdd1] = '" & Me.txtAddr1 & "'")

This works fine, or at least it did until I built in a Celtic name corrector. No problem with Mcs and Macs but when I try entering an O' name I get an error on the DLookup.

It says "Syntax error (missing operator) in query expression '[strConSname] = 'O'Dude' And [strConFname] = 'Jim' And [strConAdd1] = '16 High Street"
I think it is because of the additional apostrophe in the surname since when I change O'Dude to Jones it doesn't have a problem.

My comprehension of SQL aprostrophies is weak at the best of times but can anyone tell me how I resolve this please?
Any help greatly appreciated.
Best wishes

 

[The_Doc_Man]

You are correct that your problem is due to special treatment of the apostrophe. You cannot use the same character in a name as you attempt to use as a quoting character.

Try

"[strConSname] = """ & Me.txtSname & """ And ....

The principle is that if you want " inside "....", you write "" to represent one STORED quote. So ".."".." is stored as .."..

You could also use ` as a quoting character instead of ' ... but I think that is merely delaying the inevitable.

 

[Malcy]

Thanks Doc_man
Interestingly despite the triple " it still comes up with exactly the same message. Odd!!
Will keep trying
Best wishes

 

[RoyVidar]

Then try the replace function to double up the single quotes

"[strConSname] = '" & replace(Me.txtSname,"'","''") & "' And ...

 

[Malcy]

Thanks for suggestion but sadly it still does the same thing.
Must somewhere be some Irish based databases that deal with this on an everyday basis.
Thanks for trying though!
Best wishes

 

[Malcy]

Hey guys
Thanks and I have now got it to work using Pat's suggestion but also reviewing my own stupidity since I recalled seeing Pat's idea before and felt sure that you guys had to have it right.
OK so admission time.
When I got the error message I searched the code and found the DLookup with the same syntax as the error message.
Then in testing following the apparent failure of even Pat's suggestion, I suddenly found that the code was going further than I had previously thought. On digging deeper I found (shock horror) I had a second DLookup with almost identical functionality towards the end of the code!
So here I was focussing on the first lookup and thinking I hadn't found a solution when I suspect the first suggestion from Doc_Man would have solved it if I had used it both times!!
Many apologies and humble thanks for your patience and help.
Will go and quietly flagellate myself to a pulp for being so stupid!!
Best wishes

 

[colette]

Apostrophe's in Names

Hi,
Here is another way, equally annoying though...

Chr(34) is the ascii code for apostrophe.

field=" & Chr(34) & me!txtname & Chr(34)


Colette