Select Query Criteria

[SITCFanTN]

I have a table that I need to query to pull out all account numbers that have
the characters 921 as the 11th, 12th and 13th character. The table has
90,000+ records a sampling of the format of the account numbers are:

123456789-921-1
234234234-921-4
765874345-921-1
938586747-921-1
354921988-921-1

If I use the criteria of Like "*921" it pulls records where 921 appears
in the first 9 characters as well; I just want to select the records with 921
between the dashes...any idea how I would write the criteria for this?
Thanks for your help.

 

[Allen Browne]

Try typing this in the Field row:
Mid([Field1], 11, 3)
substituting your field name for Field1.

In the Criteria row under this, enter:
"921"

Alternatively, you could use:
Like "#########-921*"
but this might not work:
http://support.microsoft.com/kb/271661

 

[John Spencer]

Or even use

LIKE "*-921-*"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Try typing this in the Field row:
Mid([Field1], 11, 3)
substituting your field name for Field1.

In the Criteria row under this, enter:
"921"

Alternatively, you could use:
Like "#########-921*"
but this might not work:
http://support.microsoft.com/kb/271661

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table that I need to query to pull out all account numbers that
have
the characters 921 as the 11th, 12th and 13th character. The table has
90,000+ records a sampling of the format of the account numbers are:

123456789-921-1
234234234-921-4
765874345-921-1
938586747-921-1
354921988-921-1

If I use the criteria of Like "*921" it pulls records where 921
appears
in the first 9 characters as well; I just want to select the records with
921
between the dashes...any idea how I would write the criteria for this?
Thanks for your help.

 

[Allen Browne]

Or even use

LIKE "*-921-*"

Yes, that's better if the fields are of variable length, but has the same
problem if the field is indexed.

 

[John Spencer]

Sorry, didn't mean to imply one way was better or worse. Just meant to give
an alternative. Personally I prefer avoiding VBA functions when I can.
Although, perhaps MID isn't a JUST a VBA function and is (can be) native to
JET SQL - similar to IIF.

Guess that would be something I need to investigate
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..