Select precise words in an Access Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Selecting an "Address" Field from a Table, I enter an Asterix, then Upper
Case letter - say an A - , and then another Asterix in the 'Criteria' area.
The end result is Like "*A*"

When the Query is RUN, it returns all 'Street Names' containing an A whether
it is in Upper or Lower Case. Previous versions would only return names
containing an Upper Case Letter - usually the first character in the Street
Name.

How can one limit the "Character" selection in Access 2003?
 
Selecting an "Address" Field from a Table, I enter an Asterix, then Upper
Case letter - say an A - , and then another Asterix in the 'Criteria' area.
The end result is Like "*A*"

When the Query is RUN, it returns all 'Street Names' containing an A whether
it is in Upper or Lower Case. Previous versions would only return names
containing an Upper Case Letter - usually the first character in the Street
Name.
Click to expand...

Ummm... no. Access (the JET query engine to be exact) has NEVER been
case-sensitive, from Access 1.0 on. Are you thinking about some other
database software perhaps?

A criterion of

LIKE "* A*" OR LIKE "A*"

with a blank preceding the letter will find names preceded by a blank
or at the start of the field - "West Arthur St." or "Arcadia Blvd" -
but will also find "Bancroft Ave."
How can one limit the "Character" selection in Access 2003?
Click to expand...

Not easily - only by using the StrComp() or InStr() VBA functions. See
the online help by opening the VBA editor (with Ctrl-G) and search the
help for these functions.

John W. Vinson[MVP]
 
On further reflection that won't work but this will:

SELECT TableName.FieldName1, InStr(1,[TableName].[FieldName1],"A",0) AS
FindA
FROM TableName
WHERE (((InStr(1,[TableName].[FieldName1],"A",0))>1));

I did not clean this for the example.

John
 
Back
Top