query between two dates and times

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan Hale - 02 Dec 2005 08:25 GMT

i need to pull data from table, where the data needs to be limited to be
inbetween a specific range of dates and times like, say ill need to see only
those records that were entered sometime between 11/1/2005 4:00Pm to
11/2/2005 3:00am. (I work overnights, so i start on monday and go home
tuesday for example). in the database i have 2 columns, both are set to
date/time, one column currently holds just the date, and one holds just the
time. How do i make a query that will show me only the data that is between
say 11/14/2005 4:00pm and 11/15/2005 6:00am?

Reply to this Message

John Spencer - 02 Dec 2005 13:06 GMT

The best way would probably be to store both the date and the time in one
datetime field.

In your query add a calculated field and then search against that
Field: [MyDate] + [MyTime]
Criteria: Between #11/1/2005 4:00Pm # and #11/2/2005 3:00am#

Another way is to use more complex criteria;
Field: MyDate
Criteria: =#11/1/2005#
Criteria(Line2): =#11/2/2005#

Field: MyTime
Criteria:> =#4:00Pm #
Criteria(Line2):> =# 3:00am#

>i need to pull data from table, where the data needs to be limited to be
> inbetween a specific range of dates and times like, say ill need to see
[quoted text clipped - 7 lines]
> between
> say 11/14/2005 4:00pm and 11/15/2005 6:00am?

Reply to this Message

Dan Hale - 02 Dec 2005 17:33 GMT

Excellent! I had been using different combinations of &, & " " & to join the
date and time, but that just wasnt working. I'll have to learn the
difference's between the + and the & in access, but by using the + to join
the two fields, i am now able to do exactly what i needed to do, Thanks much!

> The best way would probably be to store both the date and the time in one
> datetime field.
[quoted text clipped - 22 lines]
> > between
> > say 11/14/2005 4:00pm and 11/15/2005 6:00am?

Reply to this Message

John Spencer - 02 Dec 2005 18:22 GMT

In this case, the + is actually ADDING the Date (which is stored as a
number) and the time (which is stored as a number) together. When you use
the & it concatenates the two together and makes them a string. The string
can be forced back to a dateTime by using the CDate function, but that can
fail if the concatenation creates a string that CDate can't convert.

> Excellent! I had been using different combinations of &, & " " & to join
> the
[quoted text clipped - 29 lines]
>> > between
>> > say 11/14/2005 4:00pm and 11/15/2005 6:00am?

Reply to this Message