Query Records of Different Formats

  • Thread starter Thread starter ttp
  • Start date Start date
T

ttp

I have a table with records with 4 different formats. Examples are 1161684S,
F6357G3, 1SX0014, and 1LX0100. I need to create 3 different queries to
return only records with a similar format:
1)all records that are 8 characters in length and end in S
2)all records that begin with F and have 6 character of G
3)all records that have the 2nd character of S or L

I was able to write an expression to return #1. The expression is
IIf(Len([allocation table_import]![ShopOrder])=8 And Right([allocation
table_import]![ShopOrder],1)="S",[allocation table_import]![ShopOrder]).

But, I have not been able to successfully write an expression for #2 and #3.
Can someone assistance me in writing the correct expression?
 
Try the following

Shoporder like "???????S"

ShopOrder Like "F????G*"

ShopOrder Like "?[LS]*"

In the query grid that would be the following to get the ones that are 8
characters long and end in S.
Field: ShopOrder
Criteria: like "???????S"

Look up "wildcards" in the help for an explanation of what each of the above does.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top