Filter Query on Last 2 digits of a text string 1

[TOTCOM11]

I am using a select query and would like to filter it using the last two letters of a 8 character product code which is found in the field [ProductCode]. For instance, I want to see all product codes that do not have a CA, NA, or AN as the last two characters of the product code. Thus a code with CX at the end WOULD be able to be seen in the query. How can I do this?

 

[Blorf]

Hi. Try using right([ProductCode], 2) to get your 2 charachters, and put a criteria in your query.

ChaZ

 

[PHV]

One way:
WHERE ProductCode Not Like '*CA' AND ProductCode Not Like '*NA' AND ProductCode Not Like '*AN'
Another way:
WHERE Right(ProductCode,2) Not In ('CA','NA','AN')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TOTCOM11]

Thank you so much PHV! I knew I had to use the "Right" function but did not know how to type up the parameters. This was an excellent and very prompt post and you have my star for it. Thanks again!

TOTCOM11

 

[TOTCOM11]

Ok, I have one more question. I want to view all codes that DO NOT have a 4 or 5 in the sixth (6th) character. For instance, the code C16002PP would be viewable but C13224PP would not be viewable in the query.

 

[PHV]

WHERE Mid(ProductCode,6,1) Not In ('4','5')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TOTCOM11]

Thanks again...you get another star! :-D What is the third parameter for the MID function that you put a 1? What does that do?

TOTCOM11

 

[PHV]

While in VBE (Alt+F11) open the immediate pane (aka debug window) (Ctrl+G), type mid and press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244