[AccessD] Using Replace to convert a Field value

You shouldn't remove the apostrophe, you should "escape" it with a second one in the 
criteria.

Dim sQ as string : sQ = Chr$(39) ' Single quote character

strCriteria = "Sitename = '" & Replace(txtSiteName, sQ, sQ & sQ) & _
                   "' AND ClientSiteID <> " & Me!txtClientSiteID

If Nz(DLookup("ClientSiteID", "tblClientSite", strCriteria)) <> 0 Then
...


On 2 Oct 2013 at 15:47, David Emerson wrote:

> 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
> 
>  
> 
>  
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>