I'm building a query that lists part #'s and their bin locations. I have a LTrim function that eliminates bin #'s with anything that has a PS on the end of it. I have my field in the query like this Ex: LTrim([BIN]) and the criteria is: Not Like '%PS'

What the problem is it's eliminating every part # with a bin location that ends with PS and everything where the field is blank.

All I want is to eliminate all items with a bin # that ends with PS and not the ones where the bin # is blank.

Are all the part numbers the same length ?

The part #'s do not have the same length. They can vary considerably.

Try this ...

Expression: Right(Trim(Nz([BIN],"")),2)
Criteria: <> "PS"

Note:

LTrim(<expression>): Trims the LEFT of <expression>
__My_Text => My_Text (Note: the "_" represents a space)

RTrim(<expression>): Trims the RIGHT of <expression>
My_Text__ => My_Text

Trim(<expression>): Trims BOTH sides of <expression>
__My_Text__ => My_Text


Edits Added:
>>> Added the Nz() function to convert NULLS into ZLS's

Edited by: datAdrenaline on Mon Feb 6 18:30:22 EST 2006.

Second Edit:
>>> The wildcard character of '%' is not used in MSAccess ... the '*' is used for multiple characters and the '?' is used for single characters.... the '%' IS used IF your query is a passthru to SQL ... IF that is the case, then the suggestion I gave will need to change since they are MSAccess functions ... Please let me know ... if this a passthru to a SQL server?

Edited by: datAdrenaline on Mon Feb 6 18:54:28 EST 2006.