Reporting on time portion of general date field

  • Thread starter Thread starter Brian Roberts
  • Start date Start date
B

Brian Roberts

I have created a security database and I would like to
create a report that displays counts on security codes
monthly or quarterly based on 4 hours intervals using the
general date field. For example:
From: 01/07/2003 To: 30/09/2003 Count
Code 1 12:00:00 AM to 04:00:00 AM 3
Code 2 04:00:00 AM to 08:00:00 AM 20
Code 3 08:00:00 AM to 12:00:00 PM 15
and so on.

Can this be done and if so, how?
 
Thanks for your help Duane! How do I, now, get the
results for the 4 hour intervals without multiple lines.
I put the fields on a Group Footer but, I still get
multiple lines. For example:

Code 1 Count
Time Interval Footer

Time Interval 0 1
Time Interval 1 0
Time Interval 2 1
Time Interval 3 1
Time Interval 4 0
Time Interval 5 1
Time Interval 0 1
Time Interval 1 0
Time Interval 2 0
Time Interval 3 0
Time Interval 4 1
Time Interval 5 0 and so on...

I tried adding the Sum function to the footer fields but,
I still get multiple lines. Here is my sum expression for
the Time Interval 0 field:

=Sum(IIf(Int(TimeValue([Date&TimeofIncident])*6)=0,1,0))

Any suggestions?
-----Original Message-----
Try a Sorting and Grouping expression like
=Int(TimeValue([YourField]) * 6)

--
Duane Hookom
MS Access MVP


Brian Roberts said:
I have created a security database and I would like to
create a report that displays counts on security codes
monthly or quarterly based on 4 hours intervals using the
general date field. For example:
From: 01/07/2003 To: 30/09/2003 Count
Code 1 12:00:00 AM to 04:00:00 AM 3
Code 2 04:00:00 AM to 08:00:00 AM 20
Code 3 08:00:00 AM to 12:00:00 PM 15
and so on.

Can this be done and if so, how?
Click to expand...


.
Click to expand...
 
What are the settings in your Sorting and Grouping dialog for all
expessions? Can't you add a text box in the group footer of:
=Count(*)

--
Duane Hookom
MS Access MVP


Brian said:
Thanks for your help Duane! How do I, now, get the
results for the 4 hour intervals without multiple lines.
I put the fields on a Group Footer but, I still get
multiple lines. For example:

Code 1 Count
Time Interval Footer

Time Interval 0 1
Time Interval 1 0
Time Interval 2 1
Time Interval 3 1
Time Interval 4 0
Time Interval 5 1
Time Interval 0 1
Time Interval 1 0
Time Interval 2 0
Time Interval 3 0
Time Interval 4 1
Time Interval 5 0 and so on...

I tried adding the Sum function to the footer fields but,
I still get multiple lines. Here is my sum expression for
the Time Interval 0 field:

=Sum(IIf(Int(TimeValue([Date&TimeofIncident])*6)=0,1,0))

Any suggestions?
-----Original Message-----
Try a Sorting and Grouping expression like
=Int(TimeValue([YourField]) * 6)

--
Duane Hookom
MS Access MVP


Brian Roberts said:
I have created a security database and I would like to
create a report that displays counts on security codes
monthly or quarterly based on 4 hours intervals using the
general date field. For example:
From: 01/07/2003 To: 30/09/2003 Count
Code 1 12:00:00 AM to 04:00:00 AM 3
Code 2 04:00:00 AM to 08:00:00 AM 20
Code 3 08:00:00 AM to 12:00:00 PM 15
and so on.

Can this be done and if so, how?
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top