Select Query Criteria

  • Thread starter Thread starter SITCFanTN
  • Start date Start date
S

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.
 
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
..

Allen Browne said:
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.

SITCFanTN said:
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.
Click to expand...
Click to expand...
 
John Spencer said:
Or even use

LIKE "*-921-*"
Click to expand...

Yes, that's better if the fields are of variable length, but has the same
problem if the field is indexed.
 
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
..
 
Back
Top