Jumat, 24 Agustus 2012

Re: [MS_AccessPros] Issue with a string search

 

A.D.

If you'll notice how flagrantly us people ignore history, the pitfalls will probably always be present. LOL

However, I'll remember your alternate suggestion. Back in the mid 90's I actually replaced quotes with a special hex character that I knew couldn't be typed prior to saving a textbox field and reversed the replacement upon displaying the data later. Using the form's textbox reference directly probably would have saved me the trouble.

So, thank you again.

Jeff

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

You are most welcome Bill!

As you would have noticed, the topic seems to come up from time to time. Adoption of suggested alternative could help avoid potential pitfalls.

A.D. Tejpal
------------

----- Original Message -----
From: Bill Mosca
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, August 22, 2012 20:55
Subject: Re: [MS_AccessPros] Issue with a string search

A.D.

For some reason, I always forget about that trick. Thanks for reminding us. It might be a bit more typing, but it takes care of those blasted quotes.

Bill Mosca

--- In MS_Access_Professionals@yahoogroups.com , "A.D. Tejpal" <adtp@...> wrote:
>
> Criteria string for applying form filter
> (No need to fix embedded quotes)
> ========================
>
> Almost invariably, criteria values having embedded quotes (single, double or a combination of both) are derived from form controls. Quite often, developers run into problems and find it somewhat hard to carry out fixing of such quotes prior to concatenation into final criteria string.
>
> In such situations, potential pitfalls can be avoided by using an alternative approach that completely does away with the need for fixing such quotes. This is done by fetching the desired value via expression embedded within the criteria string, rather than trying to first get the value, fix the quotes and then concatenate into the final string.
>
> For example in Art's project under discussion, filter string for LastName would be as follows - for exact match:
> '=============================
> strFilter = strFilter & "LastName = [Forms]![" & _
> Me.Name & "]![CboLastName]"
> '=============================
>
> For partial match, using Like operator, the statement would become:
> '=============================
> strFilter = strFilter & "LastName Like '*' & " & _
> "[Forms]![" & Me.Name & _
> "]![CboLastName] & '*'"
> '=============================
>
> Direct use of form control's raw contents is an extremely useful method. Some of its salient advantages are:
> (a) No need to fix any embedded quotes, whether single or double or a combination of both.
> (b) No need to worry about specific delimiters pertaining to concatenation of values for various data types (e.g. ' for text type and # for date type data).
> (c) No risk of dates getting mis-interpreted (in the process of concatenation into SQL string) when local system settings for short date are not as per US date format (mm/dd/yyyy).
>
> Best wishes,
> A.D. Tejpal
> ------------
>
> ----- Original Message -----
> From: Art Lorenzini
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Wednesday, August 22, 2012 00:22
> Subject: Re: [MS_AccessPros] Issue with a string search
>
>
> John,
>
> That was the ticket. THank you very much. Final code:
>
> Private Sub RunFilter()
> Dim strFilter As String
> Dim bFilter As Boolean
>
> bFilter = False
> strFilter = ""
>
> If Nz(Me.cboLastName, "<All>") > "<All>" Then 'LastName
> If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
> 'strFilter = strFilter & "LastName = '" & Me.cboLastName & "'"
>
> strFilter = strFilter & "LastName = '" & Replace(Me.cboLastName, "'", "''") & "'"
>
> bFilter = True
> End If
>
> If Nz(Me.cboFirstName, "<All>") > "<All>" Then 'FirstName
> If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
> strFilter = strFilter & "FirstLastName = '" & Replace(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
>
> With Warm Regards,
>
> Arthur D. Lorenzini
> IT Specialist
> Sioux Falls, South Dakota
> dbalorenzini@...
>
> ________________________________
> From: John Viescas <JohnV@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Tuesday, August 21, 2012 1:29 PM
> Subject: RE: [MS_AccessPros] Issue with a string search
>
> Aaarrgh. Somewhere we dropped off the ending '
>
> 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 Lorenzini
> Sent: Tuesday, August 21, 2012 8:03 PM
> To: mailto:MS_Access_Professionals%40yahoogroups.com
> Subject: Re: [MS_AccessPros] Issue with a string search
>
> A little further information. I printed strFilter in the Immediate window and this is what it contained.
>
> ? strFilter
> LastName = 'O''BRIEN
>
> This is what is in the LOCAL WIndow:
>
> : strFilter : "LastName = 'O''BRIEN" : String
>
> 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 12:44 PM
> Subject: 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: mailto:MS_Access_Professionals%40yahoogroups.com [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Art Lorenzini
> Sent: Tuesday, August 21, 2012 7:36 PM
> To: mailto:MS_Access_Professionals%40yahoogroups.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
> mailto:dbalorenzini%40yahoo.com
>
> ________________________________
> From: John Viescas <mailto:JohnV%40msn.com>
> To: mailto:MS_Access_Professionals%40yahoogroups.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

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

--

Jeffrey Park Jones
Excel, Access, Word, Office Expert
Excel and Access, LLC®
Christopher@ExcelAndAccess.Com
http://ExcelAndAccess.Com
Toll Free 877-392-3539
Direct Line 714-262-6893

919-671-9870
5109 Deer Lake Trail
Wake Forest, NC 27587
jpjones23@centurylink.net

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

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar