[AccessD] Using Replace to convert a Field value

You mean that the table may have an apostrophe but there may be none in the textbox?  
That's slightly different - you do need  to use a Replace() function  inside the SQL in that 
case.

Either escape the double quotes in the Replace() function with a second set or build the 
string with another variable

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

or

Dim sDQ as string: sDQ = chr$(34) 'Double quote

strCriteria = "Replace([LName]," & sDQ & sQ & sDQ & "," & sDQ & sDQ & ") = '" & _
                    Replace(txtSiteName, sQ, "") & _
                    "' AND ClientSiteID <> " & Me!txtClientSiteID

On 2 Oct 2013 at 16:27, David Emerson wrote:

> Thanks for the response Stuart.  The problem is with the first Sitename.
> The value in the fields from the table could have an apostrophe - it is how
> I can get the field value to match the new record value for comparison.
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
> Sent: Wednesday, 2 October 2013 4:15 p.m.
> 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
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>