query using dates

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

Guest

Hello,

I have a table that contains the fields shift_start and shift_end. Both
fields have the format set to general date and contain data like 11/11/2004
8:00AM. I want to perform a simple query that displays the data from certain
fields including the shift_start and shift_end, but only if the date is equal
to the current date, and then another query that does the same for the
current month. Both are based on the shift_start field. I thought this
would be straight forward but some reason I cannot get any records to return?
Is it because the field contains a time as well? Any help would be
appreciated.
 
Hello,

I have a table that contains the fields shift_start and shift_end. Both
fields have the format set to general date and contain data like 11/11/2004
8:00AM. I want to perform a simple query that displays the data from certain
fields including the shift_start and shift_end, but only if the date is equal
to the current date, and then another query that does the same for the
current month. Both are based on the shift_start field. I thought this
would be straight forward but some reason I cannot get any records to return?
Is it because the field contains a time as well? Any help would be
appreciated.
Click to expand...

Your analysis is correct: an Access Date/Time is stored internally as
a Double Float number, a count of days and fractions of a day since an
arbitrary start point. 11/11/2004 8:00am is actually stored as
38302.3333333333 - and if you use a Query comparing this to #11/11/04#
it won't match, since 38302.00000000 is not equal to 38302.3333333333!

Solution: put a calculated field in the Query:

DateValue([Shift_Start])

For shifts starting on a given day, a criterion of

CDate([Enter Date:])

applied to this field will work. For all shifts starting in the
current month, use a criterion of
= DateSerial(Year(Date()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)
Click to expand...


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks for your help. This worked!

John Vinson said:
Hello,

I have a table that contains the fields shift_start and shift_end. Both
fields have the format set to general date and contain data like 11/11/2004
8:00AM. I want to perform a simple query that displays the data from certain
fields including the shift_start and shift_end, but only if the date is equal
to the current date, and then another query that does the same for the
current month. Both are based on the shift_start field. I thought this
would be straight forward but some reason I cannot get any records to return?
Is it because the field contains a time as well? Any help would be
appreciated.
Click to expand...

Your analysis is correct: an Access Date/Time is stored internally as
a Double Float number, a count of days and fractions of a day since an
arbitrary start point. 11/11/2004 8:00am is actually stored as
38302.3333333333 - and if you use a Query comparing this to #11/11/04#
it won't match, since 38302.00000000 is not equal to 38302.3333333333!

Solution: put a calculated field in the Query:

DateValue([Shift_Start])

For shifts starting on a given day, a criterion of

CDate([Enter Date:])

applied to this field will work. For all shifts starting in the
current month, use a criterion of
= DateSerial(Year(Date()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)
Click to expand...


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Back
Top