How to not include results starting with 99

[SteveP.]

I have been asked by our purchasing department to modify a query on one of
their databases to not include results that have a PO # which starts with
"99" (company has been in existence for over 25 years and is only at 992317,
so I don't think any of us will be around by the time they use up all the
"99****" possibilities) as these are rework POs. Any help with this is
greatly appreciated.
Thanks in advance.

 

[Bob Barrows]

I have been asked by our purchasing department to modify a query on
one of their databases to not include results that have a PO # which
starts with "99" (company has been in existence for over 25 years and
is only at 992317, so I don't think any of us will be around by the
time they use up all the "99****" possibilities) as these are rework
POs. Any help with this is greatly appreciated.
Thanks in advance.

It depends on the datatype of the column. If numeric, you need to use

NOT BETWEEN 990000 AND 999999

If it is text, then:

NOT LIKE '99????'

 

[John Spencer]

Use criteria of
[SomeField] NOT LIKE "99*"

IF the field is a number field and not a text field
Not([SomeField] Between 990000 and 999999)

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

 

[Marshall Barton]

I have been asked by our purchasing department to modify a query on one of
their databases to not include results that have a PO # which starts with
"99" (company has been in existence for over 25 years and is only at 992317,
so I don't think any of us will be around by the time they use up all the
"99****" possibilities) as these are rework POs. Any help with this is
greatly appreciated.

What a shortsighted numbering plan. You should try to
convince them to add a field to the table that indicates
reworked POs instead of using a hokey numbering scheme like
that.

That said, you can add a filter to the query's PO number
field something like:

Not Like "99####"