case sensitive query criteria

[DZ]

Greetings

How can I make query criteria case sensitive. I want to
return records LIKE "*A*" but not LIKE "*a*".

DZ

 

[Brian Camire]

Instead of the LIKE operator, you might try using the InStr function with
zero as the last parameter.

InStr finds the position in one string of another string (or zero if the
first string doesn't contain the second, or Null if either of the strings is
Null). Using zero as the value for the last parameter, you can specify that
the comparison be case-sensitive.

For example:

InStr(1, "Big Apple", "A", 0)

finds the position of the string "A" in the string "A" starting at position
1 and using a case sensitive search, and so returns 5.

By comparison, InStr(1, "Big Apple", "a", 0) will return zero, indicating
that "Big Apple" does not contain "a".

See help for more information on the InStr function. If you're using Access
2000 or later, you'll need to view the help from the Microsoft Visual Basic
window (which you can display by selecting Tools, Macro, Visual Basic
Editor) to see the help topic for the InStr function.

 

[Brian Camire]

To use the InStr function for a case-senstive criteria in a query, you
might:

1. In query design view, enter an expression something like this in the
Field row of a blank column of the query grid:

InStr(1, [Your Field], "A", 0)

2. In the same column of the query grid, enter <>0 in the Criteria row.

This assumes you want to select records where the field named "Your Field"
contains an upper case A.