Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2007
    Posts
    15

    Red face Unanswered: Question about VBA character set

    Hello,

    I found the following pice of VB code in an Access database I am task with maintaining:

    ...
    sn As String
    ...
    If Mid(sn, 2, 1) >= "A" And Mid(sn, 2, 1) <= "z" Then

    < Do something >

    End If

    The idea is that if the second character in the string sn is an upper or lower case letter that the code will "do something". However, my read of the ASCII character set says that:

    A = 65
    ...
    Z = 90
    [ = 91
    \ = 92
    ] = 93
    ^ = 94
    _ = 95
    ' = 96
    a = 97
    ...
    z = 122

    Since A through Z is 65 throught 90 and a throught z is 97 throught 122 I would expect the characters repersented by 91 through 121 will also allow the do something code to be excuted. However, when I run the code with one of the characters [ / ] ^ _ ' in second position of the string the do something code is not executed?? Why is this?? Is there something about characters in VBA that I am not understanding??

    Thanks for the help!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    For the comparison to work the way you want you should test on the numeric code value of the characters, using the Asc function:
    Code:
    If Asc(Mid(sn, 2, 1)) >= 65 And Asc(Mid(sn, 2, 1)) <= 122 Then
    Have a nice day!

  3. #3
    Join Date
    Aug 2007
    Posts
    15
    I understand what you are saying. If I were going to rewrite the comparison or modify it in some way I would use Asc() the way you suggest. However, what is the answer to my question, why does the orginal way work??

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    What kind of Option Compare (if any) do you have in the declaration section of the module ?
    Have a nice day!

  5. #5
    Join Date
    Aug 2007
    Posts
    15
    At the top of the module I found:

    Option Explicit
    Option Compare Database

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Then you have the answer. Change it to Option Compare Binary and the test should work the way you want. However, be aware that this modification will affect every string operation (compare, replace, etc.) in the module.
    Have a nice day!

  7. #7
    Join Date
    Aug 2007
    Posts
    15
    I see what's going on how. Thanks for the info.

Posting Permissions

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