Selasa, 21 Agustus 2012

RE: [MS_AccessPros] Issue with a string search

 

Art-

A SINGLE single quote in the first argument, and two single quotes in the second. Sorry, I should have said the third argument. No spaces between the quotes, either.

Like this:

strFilter = strFilter & "LastName = '" & Replace(Me.cboLastName, "'", "''")

What you're doing is replacing the single quote with two of them. It should end up looking like:

LastName = 'O''BRIEN'

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Tuesday, August 21, 2012 7:36 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Issue with a string search

John -
I changed it to this:

strFilter = strFilter & "LastName = '" & Replace(Me.cboLastName, "'", "''")
And now the error is:

Syntax error in string query expression 'LastName = 'O'BRIEN'

With Warm Regards,

Arthur D. Lorenzini
IT Specialist
Sioux Falls, South Dakota
dbalorenzini@yahoo.com

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, August 21, 2012 12:27 PM
Subject: RE: [MS_AccessPros] Issue with a string search


Art-

That's two single quotes as the second argument of the Replace, not a double-quote.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: mailto:MS_Access_Professionals%40yahoogroups.com [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Tuesday, August 21, 2012 7:19 PM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: Re: [MS_AccessPros] Issue with a string search

John,

Ok so I search for O'BRIEN, I changed the strFilter string to what you suggested and now I get the following error:

Syntax error in string query expression 'LastName = 'O"BRIEN'

With Warm Regards,

Arthur D. Lorenzini
IT Specialist
Sioux Falls, South Dakota
mailto:dbalorenzini%40yahoo.com

________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Tuesday, August 21, 2012 11:54 AM
Subject: RE: [MS_AccessPros] Issue with a string search

Art-

Like this:

strFilter = strFilter & "LastName = '" & Replace(Me.cboLastName, "'", "''")
& "'"

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: mailto:MS_Access_Professionals%40yahoogroups.com
[mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Art
Sent: Tuesday, August 21, 2012 6:39 PM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: [MS_AccessPros] Issue with a string search

I have the folling search code:
Dim strFilter As String
Dim bFilter As Boolean

bFilter = False
strFilter = ""
Private Sub RunFilter()
If Nz(Me.cboLastName, "<All>") > "<All>" Then 'LastName
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "LastName = '" & Me.cboLastName & "'"
bFilter = True
End If

If Nz(Me.cboFirstName, "<All>") > "<All>" Then 'LastName
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "FirstName = '" & Me.cboFirstName & "'"
bFilter = True
End If

If bFilter Then
Me.sfrmClientList.Form.OrderBy = ""
Me.sfrmClientList.Form.Filter = strFilter
Me.sfrmClientList.Form.FilterOn = True
Else
Me.sfrmClientList.Form.FilterOn = False
End If
End Sub

The issue is when the name contains a single quote it throws an error otherwise
it works fine. Can you give me an idea of the issue?

Thanks

Art Lorenzini
Sioux Falls, SD

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

Yahoo! Groups Links

[Non-text portions of this message have been removed]

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

Yahoo! Groups Links

[Non-text portions of this message have been removed]

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

Yahoo! Groups Links

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar