Date query based on other fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my table, I have 3 fields--Date, Start Date, and Stop Date. Each is
different for every case. How do I query only those Date cases that fall on
or after the Start Date and on or before the Stop Date.

Thanks!
Quin
 
In the Criteria row under your Date field in query design, enter:
Between [Start Date] And [Stop Date]

BTW, it's not a good idea to use Date as a field name. In VBA, it is a
reserved word for the system date. You will probably get away with it for
this query, but in some contexts Access will get confused and treat it as
today's date instead of the value in the field.
 
Quin said:
In my table, I have 3 fields--Date, Start Date, and Stop Date. Each is
different for every case. How do I query only those Date cases that fall on
or after the Start Date and on or before the Stop Date.
Click to expand...


Just set the criteria for the Date field to:

Between [Start Date] And [End Date]
 
If I use the BETWEEN function, will it be inclusive of the Start Date and
Stop Date?

Thanks Marsh & Allen!
Quin

Marshall Barton said:
Quin said:
In my table, I have 3 fields--Date, Start Date, and Stop Date. Each is
different for every case. How do I query only those Date cases that fall on
or after the Start Date and on or before the Stop Date.
Click to expand...


Just set the criteria for the Date field to:

Between [Start Date] And [End Date]
Click to expand...
 
Yes. Between is both inclusive.

However, if the Stop Date field contains a time component (not merely a
date), then that time is AFTER the pure date, and so those values are not
included. Typically this happens if you have the field's Default Value set
to =Now(). You can use =Date() instead of you don't want time in the new
records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Quin said:
If I use the BETWEEN function, will it be inclusive of the Start Date and
Stop Date?

Thanks Marsh & Allen!
Quin

Marshall Barton said:
Quin said:
In my table, I have 3 fields--Date, Start Date, and Stop Date. Each is
different for every case. How do I query only those Date cases that fall
on
or after the Start Date and on or before the Stop Date.
Click to expand...


Just set the criteria for the Date field to:

Between [Start Date] And [End Date]
Click to expand...
Click to expand...
 
Thanks for the information, Allen.
Quin

Allen Browne said:
Yes. Between is both inclusive.

However, if the Stop Date field contains a time component (not merely a
date), then that time is AFTER the pure date, and so those values are not
included. Typically this happens if you have the field's Default Value set
to =Now(). You can use =Date() instead of you don't want time in the new
records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Quin said:
If I use the BETWEEN function, will it be inclusive of the Start Date and
Stop Date?

Thanks Marsh & Allen!
Quin

Marshall Barton said:
Quin wrote:

In my table, I have 3 fields--Date, Start Date, and Stop Date. Each is
different for every case. How do I query only those Date cases that fall
on
or after the Start Date and on or before the Stop Date.


Just set the criteria for the Date field to:

Between [Start Date] And [End Date]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top