Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Location
    Toronto, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Case sensitive criteria? (Access97)

    Is there a workaround that will enable me to use case sensitive criteria in a query?

    Currently I am able to search in my main datatable for occurences of 'h' (distinct from 'H'), and I would like to utilize this search functionality in a query.

    Many thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Case sensitive criteria? (Access97)

    <hr>Currently I am able to search in my main datatable for occurences of 'h' (distinct from 'H'), <hr>
    I'm sorry, but I'm not clear on what you mean by this. Access is not case sensitive by design. The only thing I can think of would be to search for the ASCII value of a letter, since those values differ between lower and upper case.

    Why are you trying to do a case sensitive search in a query/table? Perhaps there's another way to accomplish your objective.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Apr 2001
    Location
    Toronto, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Case sensitive criteria? (Access97)

    Hello Charlotte,

    Thanks for your reply.

    I am trying to search through a 4 character alpha-numeric field for all records where the 2nd character is a lowercase 'h'. This field will have many other records where the first character is either 'H' or 'h' as well as a number of records where the 2nd character is an uppercase 'H'. The field uses some arcane alpha/num code to indicate shifts worked for payroll, and I am trying to isolate certain specific shifts.

    Thanks for you time.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Case sensitive criteria? (Access97)

    In the criteria area of your query enter: Mid([yourfield],2,1)="h"

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

    Re: Case sensitive criteria? (Access97)

    Try Asc(Mid([yourfield],2,1))="104"
    Francois

  6. #6
    New Lounger
    Join Date
    Apr 2001
    Location
    Toronto, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Case sensitive criteria? (Access97)

    Thank you Brian.

    Although this will identify records where the 2nd character is 'h', it also picks up the uppercase 'H'.

    From an initial recordset of about 32,000 records, records with 'h' and 'H' number about 900, while a case sensitive search for 2nd character 'h' would yield only the 25 or so records I am looking for.

    Any other suggestions?

    Thanks,

    Rob.

  7. #7
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Case sensitive criteria? (Access97)

    Sorry, Francois is correct <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> I didn't test it properly.

  8. #8
    New Lounger
    Join Date
    Apr 2001
    Location
    Toronto, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Case sensitive criteria? (Access97)

    Thank you Francois.

    Your suggestion generates an "Invalid procedure call." error message when I use it as the criteria.

    The field in question is Field9, and I have entered the criteria as follows: Asc(Mid(Field9,2,1))="104" (with square brackets around Field9).

    Currently Field9 is set up as a text field, does this matter?

    Am I doing something incorrectly?

    Thanks,

    Rob.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Case sensitive criteria? (Access97)

    ASCII values are numeric. Remove the quotes from around the value. Lake it Asc(Mid([Field9],2,1))=104.
    Charlotte

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

    Re: Case sensitive criteria? (Access97)

    Rob

    This is because you have records where field9 is empty or there is less the 2 characters
    Look in the attachment db in query1
    Attached Files Attached Files
    Francois

Posting Permissions

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