Apostrophe

[Lyncroft]

Sorry about this. Know it's a frequent question and I've searched the archives and got half the answer.

I've a DLookUp that prevents a user entering an organisation with the same first line of address:

If Not IsNull(DLookup("[Organisation]", "tblOrganisations", "[Organisation]= """ & Me![Organisation] & """ And [Address1]= """ & Me![Address1] & """))

I learnt that to accomodate apostrophes for the organisation I had to enclose them in 3 double quotes. To accomodate apostrophes on the Address1 I did exactly the same but get the syntax error message 3075.

Any ideas?

 

[bjackson]

you are missing an &
If Not IsNull(DLookup("[Organisation]", "tblOrganisations", "[Organisation]= """ & Me![Organisation] & """ & "And [Address1]= """ & Me![Address1] & """))

 

[Lyncroft]

Cheers for that. Unfortunately, I'm getting the dreaded run time 13 message!

 

[bjackson]

try this
If Not IsNull(DLookup("[Organisation]", "tblOrganisations", "[Organisation]= " & """" &

Me![Organisation] & """" & "And [Address1]= " & """" & Me![Address1] & """" ))

 

[Lyncroft]

Hello again. Thanks for taking the time out. Doesn't like that at all. Compile error.

The annoying thing is I originally had this:

If Not IsNull(DLookup("[Organisation]", "tblOrganisations", "[Organisation]= """ & _
Me![Organisation] & """ And [Address1] = '" & Me![Address1] & "'")) Then
MsgBox "That organisation already exists. Return to organisation search to check", vbCritical

This worked fine and picked up on the apostrophe on the organisation name. Splendid I thought, until I realised that address1 has apostrophes too!!! That's where the problem seems to hinge. Logically I thought putting the 2 quotes around Me.Address would suffice but it doesn't!

 

[Lyncroft]

.....sorry, that should read "3" quotes

 

[bjackson]

i tested this expression with no problems.i am using access 2000
i must say this that i have never had any success with 3 " i have
always had to use 4
,perhaps you you try 3 in my expression
(DLookup("[Organisation]", "tblOrganisations", "[Organisation]= " & """" & Me![Organisation] & """" & "And [Address1]= " & """" & Me![Address1] & """"))

 

[Lyncroft]

Hi - tremendous it worked with your 4 quotes, not with 3 which is a bit worrying!

Thanks again for your help. Never have got there on my own. I'll shout you a beer if you're ever in London.

Cheers again