[AccessD] Using Replace to convert a Field value

I have a field called SiteName.  When a new record is added I want to check
if the value in the SiteName field already exists in another record.

I use a DLookup to check for it.  The problem is that the value in SiteName
could contain an apostrophe.  This causes a problem when the DLookup is run.

I think I need to be able to remove any apostrophe's in the SiteName field
before the comparison.  I could create another query which does this and do
the DLookup with the query but I was wondering if I could do it direct with
the table.  I tried this (highlighted in red) but get "Syntax Error.
Missing Operator" error message.  

 

If Nz(DLookup("ClientSiteID", "tblClientSite", " Replace([SiteName], ''''',
'''') = '" & Replace(Nz(Me!txtSiteName, ""), "'", "") & "' AND ClientSiteID
<> " & Me!txtClientSiteID), 0) <> 0 Then

. Do something}

 

Any thoughts.

 

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand