query between two dates and times

[Guest]

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?

 

[John Spencer]

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#

 

[Guest]

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.

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
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?

 

[John Spencer]

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
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.

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
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?