HOW TO QUERY BASED ON A PORTION OF A FIELD

[azprerunner]

I have a text field that contains 14 characters

example: AA99BB11AA1234

I need to be able to run a query to select only the records that contain "11" in the 7 and 8 character positions. The 7 and 8 character positions indicate a year of production in the field. The problem I have is that if an "11" happens to also fall in the 3 and 4 or the 11, 12, 13, and/or 14 positions, it will also list that record if I use:

like "*11*"

Any suggestions?

 

[smedvid]

Can't u use the MID() or INSTR() function to parse the field in a Where clause to look for a fixed position in the string?

FYI: Upper case is considered shouting, and some may not respond based on this....

htwh,





Steve Medvid
IT Consultant & Web Master

 

[PWise]

where mid(fieldname,7,2)=11

 

[dhookom]

Since Mid() is a text/string function, I would use:

where mid(fieldname,7,2)="11"

Duane
Hook'D on Access
MS Access MVP