Criteria using part date

[Guest]

We have a client ordering numbering system like A nnnn/yy where yy denotes
the last two digits of the year. I want to be able to retrieve this year's
orders. The problem is that some orders are A nnnn/05 eventhough the date on
the order is 2006. So if I retrieve by Year(Now()) I will also get the /05
order numbers that have this year's date. If I exclude the /05 orders by Not
Like "*/05", another user may not know that.
Is there a generic way?
Thanks for looking
CurtainMary

 

[Rick Brandt]

We have a client ordering numbering system like A nnnn/yy where yy
denotes the last two digits of the year. I want to be able to
retrieve this year's orders. The problem is that some orders are A
nnnn/05 eventhough the date on the order is 2006. So if I retrieve by
Year(Now()) I will also get the /05 order numbers that have this
year's date. If I exclude the /05 orders by Not Like "*/05", another
user may not know that.
Is there a generic way?
Thanks for looking
CurtainMary

You haven't explained which of those two options represents your idea of "this
year's orders". I also don't understand what you mean by "another user may not
know that". Whichever criteria accurately represents "this year's orders" is
the one you should use.

 

[Guest]

You haven't explained which of those two options represents your idea of "this
year's orders". I also don't understand what you mean by "another user may not
know that". Whichever criteria accurately represents "this year's orders" is
the one you should use.

Hello Rick. thanks for taking the time. I wish only to retrieve the /06
orders. By "another user" I mean another user of the database who may not
have sufficient knowledge to change the underlying query criteria for a
report next year.
I would like to make it a running retrieve so that there is no need to
change the query for next and subsequent years.
Thanks again
CurtainMary

 

[Rick Brandt]

Hello Rick. thanks for taking the time. I wish only to retrieve the
/06 orders. By "another user" I mean another user of the database who
may not have sufficient knowledge to change the underlying query
criteria for a report next year.
I would like to make it a running retrieve so that there is no need to
change the query for next and subsequent years.

Well, applying criteria to the last two characters of a field will never be
particularly efficient since an index cannot be used. Technically your design
is flawed because you are storing multiple pieces of data in a single field.
However; The following should work to return all records where the last two
characters are equal to the last two digits of the year...

SELECT *
FROM TableName
WHERE Right(FieldName,2) = Format(Date(), "YY")

 

[Guest]

Thanks Rick, the excuse I offer is that this numbering was dreamt up many
years ago and lots of paperwork is attached to this.
Thanks for taking the time.
CurtainMary