Time from Date in Report

[ehunter]

Hi. I have a telephone log database that has separate date and time fields
for each record. I need to be able to run a report that shows all records
from a certain start date & time to a certain end date & time.

I tried setting up a parameter query using Between...And in the Criteria row
of the Date and Time fields to gather the records and it works fine if the
start and end dates are the same, but returns inaccurate results if the dates
are different. How could I link the start time with the start date and the
end time to the end date so that all records between the 2 date/time
parameters are returned? Thanks.

 

[Douglas J. Steele]

You really should store date and time in the same field: it avoids many
problems!

If you can't go back and correct your table, create a query that joins the
date and time (it's just a simple addition of the two fields), and use the
query rather than the table.

 

[John W. Vinson]

Hi. I have a telephone log database that has separate date and time fields
for each record. I need to be able to run a report that shows all records
from a certain start date & time to a certain end date & time.

I tried setting up a parameter query using Between...And in the Criteria row
of the Date and Time fields to gather the records and it works fine if the
start and end dates are the same, but returns inaccurate results if the dates
are different. How could I link the start time with the start date and the
end time to the end date so that all records between the 2 date/time
parameters are returned? Thanks.

An Access Date/Time value is stored as a Double Float number, a count of days
and fractions of a day since midnight, December 30, 1899. E.g.

?now
5/9/2007 6:54:57 PM
?Cdbl(Now)
39211.7882291667

(taken a few seconds apart).

As such, your Date field corresponds to midnight at the beginning of each day.
Your Time field corresponds to a time on December 30, 1899. If you apply a
range criterion to the time field, say between 10am and 1pm, then you lose the
date information - ALL times in your table are on the same date (12/30/1899!)

If you can't combine the two fields in your table - which I'd really advise -
then use a calculated field

CallDTime: [CallDate] + [CallTime]

This will work with a BETWEEN criterion even across days.

John W. Vinson [MVP]

 

[ehunter]

Thanks very much for your recommendation. I set up the query as you suggested
and it works perfectly. As the database is fairly new and in the test phase
now, I'll do as you suggest and redo the table and queries with one field for
date/time. I'll also keep date/time in only 1 field in future db's.

 

[ehunter]

Thanks for your response, John. I would prefer to use the single field
approach. I will try using a Make Table query with an extra field combining
[Date] + [Time] to make a new table with a new field combining date/time and
then deleting the individual Date and Time fields. Then I will rewrite the
queries, forms, etc. to use the single field.

Is this approach OK in your opinion or are there any potential problems to
look out for?
--
Ed

Hi. I have a telephone log database that has separate date and time fields
for each record. I need to be able to run a report that shows all records
from a certain start date & time to a certain end date & time.

I tried setting up a parameter query using Between...And in the Criteria row
of the Date and Time fields to gather the records and it works fine if the
start and end dates are the same, but returns inaccurate results if the dates
are different. How could I link the start time with the start date and the
end time to the end date so that all records between the 2 date/time
parameters are returned? Thanks.

An Access Date/Time value is stored as a Double Float number, a count of days
and fractions of a day since midnight, December 30, 1899. E.g.

?now
5/9/2007 6:54:57 PM
?Cdbl(Now)
39211.7882291667

(taken a few seconds apart).

As such, your Date field corresponds to midnight at the beginning of each day.
Your Time field corresponds to a time on December 30, 1899. If you apply a
range criterion to the time field, say between 10am and 1pm, then you lose the
date information - ALL times in your table are on the same date (12/30/1899!)

If you can't combine the two fields in your table - which I'd really advise -
then use a calculated field

CallDTime: [CallDate] + [CallTime]

This will work with a BETWEEN criterion even across days.

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks for your response, John. I would prefer to use the single field
approach. I will try using a Make Table query with an extra field combining
[Date] + [Time] to make a new table with a new field combining date/time and
then deleting the individual Date and Time fields. Then I will rewrite the
queries, forms, etc. to use the single field.

Is this approach OK in your opinion or are there any potential problems to
look out for?

It's certainly a reasonable - even cautious - approach.

John W. Vinson [MVP]