[AccessD] Using Replace to convert a Field value

That is a nice approach.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Wednesday, 2 October 2013 1:15 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com