p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: Searching through a text field in a Recordset whose first character is a single quote


Message #1 by "Lloyd Levine" <levinll@m...> on Wed, 20 Jun 2001 20:39:07
I'm attempting to search through a recordset looking at a specific field 

(that's defined as a text one) that may have a single quote as it's first 

character. I normally use the following code to searching through a text 

based field, but it's not working.



Public Function SearchField(SearchString as String) As String

Dim stLinkCriteria As String

Dim db As Database

Dim rc As Recordset



    Set db = CurrentDb

    Set rc = db.OpenRecordset("ATable")

    stLinkCriteria = "[TextField] = '" & SearchString & "'"    

    rc.FindFirst stLinkCriteria



Any idea why ? I'm assuming it has to do with the fact that some of the 

fields have a single quote as the first character.

Message #2 by "John Ruff" <papparuff@c...> on Wed, 20 Jun 2001 15:09:34 -0700
Change your stLinkCriteria to



stLinkCriteria = " TextField = """ & SearchString & """"



John Ruff - The Eternal Optimist :)



 -----Original Message-----

From: 	Lloyd Levine [mailto:levinll@m...]

Sent:	Wednesday, June 20, 2001 8:39 PM

To:	Access

Subject:	[access] Searching through a text field in a Recordset whose first

character is a single quote



I'm attempting to search through a recordset looking at a specific field

(that's defined as a text one) that may have a single quote as it's first

character. I normally use the following code to searching through a text

based field, but it's not working.



Public Function SearchField(SearchString as String) As String

Dim stLinkCriteria As String

Dim db As Database

Dim rc As Recordset



    Set db = CurrentDb

    Set rc = db.OpenRecordset("ATable")

    stLinkCriteria = "[TextField] = '" & SearchString & "'"

    rc.FindFirst stLinkCriteria



Any idea why ? I'm assuming it has to do with the fact that some of the

fields have a single quote as the first character.



Message #3 by "Pardee, Roy E" <roy.e.pardee@l...> on Wed, 20 Jun 2001 14:21:30 -0700
If SearchString ever has a single quote in it that will have the effect of

prematurely terminating your search string.  You can do a debug.print

SearchString to see whether that's happening.  If it is, your cure is to

double-up the single quote chars in SearchString--one quote 'escapes' the

other, as they say.  If you're using A2K or later, 



SearchString = Replace(SearchString, "'", "''")



will do the trick.



Cheers,



-Roy



-----Original Message-----

From: Lloyd Levine [mailto:levinll@m...]

Sent: Wednesday, June 20, 2001 1:38 PM

To: Access

Subject: [access] Searching through a text field in a Recordset whose

first character is a single quote





I'm attempting to search through a recordset looking at a specific field 

(that's defined as a text one) that may have a single quote as it's first 

character. I normally use the following code to searching through a text 

based field, but it's not working.



Public Function SearchField(SearchString as String) As String

Dim stLinkCriteria As String

Dim db As Database

Dim rc As Recordset



    Set db = CurrentDb

    Set rc = db.OpenRecordset("ATable")

    stLinkCriteria = "[TextField] = '" & SearchString & "'"    

    rc.FindFirst stLinkCriteria



Any idea why ? I'm assuming it has to do with the fact that some of the 

fields have a single quote as the first character.



Message #4 by Gee Vee <happygv@y...> on Wed, 20 Jun 2001 22:03:00 -0700 (PDT)
Hi,



I think you are currently using Single quote as the

outer quotation for embedding the value of the

searchstring variable. Pls use the double quote as the

outer quotation, this should work.



regards

Vijay.G





--- Lloyd Levine <levinll@m...> wrote:

> I'm attempting to search through a recordset looking

> at a specific field 

> (that's defined as a text one) that may have a

> single quote as it's first 

> character. I normally use the following code to

> searching through a text 

> based field, but it's not working.

> 

> Public Function SearchField(SearchString as String)

> As String

> Dim stLinkCriteria As String

> Dim db As Database

> Dim rc As Recordset

> 

>     Set db = CurrentDb

>     Set rc = db.OpenRecordset("ATable")

>     stLinkCriteria = "[TextField] = '" &

> SearchString & "'"    

>     rc.FindFirst stLinkCriteria

> 

> Any idea why ? I'm assuming it has to do with the

> fact that some of the 

> fields have a single quote as the first character.



--------------------------------------------

Vijay Kumar. G - Web Programmer

Unimobile, Inc. - "Enterprise Wireless Data Solutions"

http://www.unimobile.com

Phone: +xx-xx-xxxxxxx Ext 115

(Personal E-Mail ID - happygv@y...)

---------------------------------------------




  Return to Index