Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using apostrophes in filters (VB6/Access 2K)

    In my VB application I'm taking the recordset and doing a filter on it to get some information. This works fine until it comes across a string value with an apostrophe. I know why this is occuring, but how do I code around that?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    You can build your string as follow:
    <pre>strCriteria = "[MyField] = " & Chr(34) & "Some's" & Chr(34)</pre>

    Francois

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    Thanks Francois. This should have worked. When I looked at it in the debug window the syntax looked absolutely correct. Can you see what is wrong in the FILTER = section.


    Private Sub cmdSave_Click()

    '''Dim cn As New ADODB.Connection

    Dim rs As New ADODB.Recordset

    On Error GoTo ErrorHandler

    '************************************************* ***************
    'Section to handle Diagnosis if provided
    '************************************************* ***************


    lblStatus.Caption = "Processing . . ."

    Screen.MousePointer = vbHourglass

    Me.Refresh


    If Not (cboDiag.Text = "" Or IsNull(cboDiag.Text)) Then


    cn.Open sConnString


    With rs
    .Source = "SELECT Diagnosis FROM tblDiagnosis ORDER BY Diagnosis"
    .CursorType = adOpenDynamic
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    End With

    rs.Open , cn, , , adCmdText

    Debug.Print HandleQuotes(cboDiag.Text, "'")

    ' rs.Filter = "Diagnosis = '" & cboDiag.Text & "'"

    rs.Filter = "Diagnosis = " & Chr(34) & cboDiag.Text & Chr(34)


    If rs.RecordCount = 0 Then

    With rs

    .Filter = adFilterNone
    .AddNew
    !Diagnosis = cboDiag.Text
    .Update
    .Requery

    End With

    'Call RefreshDiag

    ' rs.Filter = adFilterNone

    End If 'rs.RecordCount = 0 Then



    End If 'Not (cboDiag.Text = "" Or IsNull(cboDiag.Text)) Then

    rs.Filter = adFilterNone

    '************************************************* ***************
    'Record Data
    '************************************************* ***************



    If cn.State = 1 Then
    cn.Close
    End If

    '''Set cn = Nothing


    If bUpdateMode = False Then
    Call SaveFormData
    Else
    Call UpdateFormData
    End If

    Exit_cmdSave_Click:

    Set rs = Nothing

    If cn.State = 1 Then
    cn.Close
    End If

    cmdSave.Enabled = False
    cmdDelete.Enabled = True
    bIsDirty = False

    lblStatus.Caption = "Ready"

    Screen.MousePointer = vbNormal

    Exit Sub

    ErrorHandler:

    MsgBox "Error #" & Err.Number & " " & Err.Description, vbCritical, "Error cmdSave_Click"

    Resume Exit_cmdSave_Click


    End Sub

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    I'm not familiar with ado but I would change the code to<pre>With rs
    .Source = "SELECT Diagnosis FROM tblDiagnosis WHERE Diagnosis = " & Chr(34) & cboDiag.Text & Chr(34)
    .CursorType = adOpenDynamic
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    End With
    rs.Open , cn, , , adCmdText
    If rs.RecordCount = 0 Then
    ...</pre>

    Francois

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    The way I'm using the data from the recordset requires I use the filter. I mean I can use another recordset but it would mean making another round trip to the database.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    Hi Mike,
    Does it work if you use:
    rs.Filter = "Diagnosis = """ & cboDiag.Text & """
    instead?
    (Incidentally, I'd probably store cboDiag.Text in a string variable rather than referring to it repeatedly.)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    It still had the problem with the apostrophe in the string. If I were to do a debug.print, copy and paste the value defining that filter into an Access query on the back end, it pulls it up just fine. The syntax looks correct with the code you gave, it is just being persistent about that apostrophe. . .

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    Are you getting an error message of any sort or does it simply not retrieve any records?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    Very strange : when you use Find or Filter the string with apostrophe is not found.
    If you loop through the recordset and compare the string (containing apostrophe) to the field, it is well found. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Francois

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    Hi Rory, Yes I am getting an error stating that the arguments are of the wrong type, are out of acceptable range, or are in conflict with one another (Error 3001).

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    Looping through the recordset is an option, not preferable, but better than making another trip to the database.

  12. #12
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    For a problem that must happen to every ADO developer on Earth, it is surprisingly difficult to find this trick!

    MS has a VB code sample for a class/ActiveX DLL that doubles the apostrophe: <A target="_blank" HREF=http://msdn.microsoft.com/library/en-us/dnsqlsg/html/msdn_validatedata.asp>A Class to Prepare Strings for Submission to a Database</A>

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    So very close, Rory. This time the program didn't error out, however, it recorded the entry as #Non Hodgkin's Lymphoma (NHL)#

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    Mike,
    What exactly do you mean by "recorded it"? Do you mean it entered a new record with that value? If so, can you post the code you're using now? (The # signs were only for use in building the filter string)
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using apostrophes in filters (VB6/Access 2K)

    <P ID="edit" class=small>Edited by rory on 11-Feb-02 08:34.</P>Hi Mike,
    I think you've got two options. Either run some code to double the apostrophe in the string you're searching for (which should cause ADO to treat it as a literal) or use the following syntax:
    rs.filter "Diagnosis = #" & cboForgotTheName.Text & "#"
    The latter will handle the eventuality that there's more than one apostrophe in the text (which the former won't) but has the drawback that there can't be a '#' sign in the text - I don't know if that could be an issue for you.
    Hope that helps.

    **Later edit:
    My statement that the former method wouldn't work for multiple apostrophes was based on something I read - having experimented this morning, it actually seems to work OK for more than one apostrophe. - Rory 11/2/2002**
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •