Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Search box error (2K)

    I have a form that includes an unbound text box that I use to lookup the names of donors. As I was testing the search box, I ran into an error on certain names. I couldn't see anything peculiar about the names until I started searching on this forum. I read HansV's post number 153111, where he mentions that an apostrophe in a name can throw Access off track. (The darnedest little things will do that!!) I've read many of the suggestions, and I'm still a bit dazed. So...here's the code behind my text box. (BTW: I got this code straight outta Helen Feddema's "Access 2002 Inside Out.")

    Dim strSearch As String

    strSearch = "[DonorName] = " & Chr$(39) & _
    Me![cboSelectDonor] & Chr$(39)

    'Find the record that matches the control.
    Me.Requery
    Me.RecordsetClone.FindFirst strSearch
    Me.Bookmark = Me.RecordsetClone.Bookmark

    Now, what must I change to make these error messages go away? Some of the posts mention using Chr(34). You'll notice I've got Chr(39) in my code--I confess I don't have the first clue what Chr(anything) means. Could someone shed some light on what this mumbo-jumbo does?

    Thanks a heap!!

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Search box error (2K)

    The Chr(34) is a double quote, the Chr(39) is a single quote.
    If you have a single quote in your data somewhere you should be using Chr(34) rather than Chr(39). If you have both you will have to do the following:
    strSearch = "[DonorName] = " & Chr$(39) & Chr$(39) & _
    Me![cboSelectDonor] & Chr$(39) & Chr$(39)
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Search box error (2K)

    Now it makes sense, in a binary sort of way! Thanks, Hans--that's the best explanation of this mysterious stuff I've seen in a long time. They ought to make this a Star Post! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  4. #4
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search box error (2K)

    I concur, this is the best description of this that I've seen. Thanks, Hans.

    I did just have a wry smile at your comment that Access would complain about Gates - it joins, I think, a rather long list of people p****d off with him!!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Chr() function and quotes within strings (all)

    Lucas,

    All characters we type have a numeric code. The letter "A" for example, corresponds to the number 65. In your computer's memory or on you computer's hard disk, the "A" is stored as the binary representation of that number 65 in 0's and 1's.

    Visual Basic has two functions that help us convert between characters and numbers: Chr(65) or Chr$(65) returns the character that corresponds to the number 65, and Asc("A") returns the number that corresponds to the character "A". You can try this out for yourself by activating the Visual Basic Editor, opening the Immediate window (Ctrl+G) and typing
    ? Chr(99)
    or
    ? Asc("z")
    followed by Enter.

    We use double quotes in Visual Basic to indicate where a string begins and ends:

    strLastName = "Gates"

    SQL uses single quotes to delimit strings, but it also accepts double quotes - but you must be consistent. For example,

    WHERE LastName = "Gates'

    is not valid.

    What should we do when a string must contain quotes? If we use double quotes, this leads to confusion with the quotes that delimit the string:

    strCondition = "WHERE LastName = "Gates""

    is invalid, for "WHERE LastName = " is seen as a complete string, causing Access to complain about Gates. There are several ways around this. In strings we type in ourselves, we can use double double quotes, or (if it has to do with SQL) single quotes. So a valid version of the instruction above is

    strCondition = "WHERE LastName = ""Gates"""

    or

    strCondition ="WHERE LastName = 'Gates'"

    Now, the numeric value (ASCII code or ANSI code) for a double quote is 34, so " = Chr(34), and the numeric value for a single quote is 39, so ' = Chr(39). These can also be used to include quotes in a string:

    "WHERE LastName = " & Chr(34) & "Gates" & Chr(34)

    In your case, the instruction

    strSearch = "[DonorName] = " & Chr$(39) & _
    Me![cboSelectDonor] & Chr$(39)


    is a VBA instruction building a where-condition. We must use double quotes in VBA, but we can use single quotes within the condition, because that is SQL. Chr(39) can be used here as long as there are no names containing a single quote, such as O'Neil. If you do have such names, you must use Chr(34). If you have both single and double quotes within names, it gets tricky.

Posting Permissions

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