Date criteria

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: BruceM (BruceM_at_discussions.microsoft.com)
Date: 01/05/05 

Date: Wed, 5 Jan 2005 10:41:07 -0800

A query includes an expiration date that can be several years in the future.
However, every year on the expiration month and day a notice needs to be
sent. The field in question is [EXP DATE]. For what its worth I would not
have a field name with a space, but I inherited the database. For the
criteria to retrieve only records with an expiration date between one month
in the past and one month in the future I use this:

Between DateSerial(Year(Date())+1,Month(Date())-1,Day(Date())) And
DateSerial(Year(Date())+1,Month(Date())+1,Day(Date())-1)

In the next three lines I repeat the criteria, except with +2, +3, and +4
for the Year part of DateSerial. +4 is as high as I ever need to go. It
works, but it seems awfully clumsy. I spent a long time trying to get
DateSerial to help me, but it can't since the results of the function
apparently are not evaluated as a date. For instance, instead of the above
criteria in [EXP DATE] I applied criteria to filter out records with the
expiration date this year, then I added a calculated field to the query:

ReviewDate: DateSerial(Year(Date()),Month([EXP DATE]),Day([EXP DATE]))

This produces 2/12/05 if the expiration date is 2/12/06 or 2/12/07, for
instance. Looks good, but worthless. When I attempted to apply criteria I
received a Data type mismatch in criteria expression error message. Here is
the criteria I attempted:

Between DateSerial(Year(Date()),Month([EXP DATE])-1,Day([EXP DATE])) And
DateSerial(Year(Date()),Month([EXP DATE])+1,Day([EXP DATE])-1)

I also created query fields using DatePart to extract Month and Day, and
used those fields in the criteria instead of [EXP DATE], but again it did not
work. Can what I am attempting be done?

Relevant Pages

  • Re: Date criteria
    ... I added ReviewDate to the query as a calculated field: ... It looks right (if the expiration date is 1/15/06, ... Day) or DateAdd as the criteria for ReviewDate ...
    (microsoft.public.access.queries)
  • Re: query for tag renewal
    ... You will see all records with an expiration date in that range. ... The above criteria gave me all tagexpirations in the table. ... When you make a query based on the table containing ExpDate, ...
    (microsoft.public.access.queries)
  • Re: Date criteria
    ... This is a great program for making global changes in an Access database. ... > A query includes an expiration date that can be several years in the ... every year on the expiration month and day a notice needs to be ... > criteria to retrieve only records with an expiration date between one ...
    (microsoft.public.access.queries)
  • Re: Date criteria
    ... > A query includes an expiration date that can be several years in the future. ... When I attempted to apply criteria I ... > received a Data type mismatch in criteria expression error message. ...
    (microsoft.public.access.queries)
  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... YES for all of the combo boxes.) ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)