[AccessD] Using Replace to convert a Field value

Thanks Stuart - I was getting my ' and " mixed up (I thought that two
apostrophe's worked the same as two quote marks - maybe that is SQL Server?)

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand



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


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