Count dates

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

Guest

I have a report that lists how many times a day a driver delivers another
load, I would like to count the number of days in the report it could be
three days etc... but the date is listed multiple times due to the number of
loads. Can I do this on my report with a distinct.date query?
 
Do you have a Group Header for each day? If not, create one (Sorting And
Grouping box in View menu), and set the section's Visible property to No if
you do not wish to see it.

Then add a text box to this section, and set these properties:
Control Source =1
Running Sum Over Group
Format General Number
Name txtDayCount
Visible No

Then in your group footer, add a text box with Control Source of:
=[txtDayCount]
 
You can create a query or queries that result in a single record with the
number of distinct dates. Add this to your report's record source and you
can display the value in your report.
 
I can't seem to make it work, I am not sure what Control Source =1 means I
have tried just as is and get zeros. All the rest makes sense, what could I
be doing wrong? Thanks

Allen Browne said:
Do you have a Group Header for each day? If not, create one (Sorting And
Grouping box in View menu), and set the section's Visible property to No if
you do not wish to see it.

Then add a text box to this section, and set these properties:
Control Source =1
Running Sum Over Group
Format General Number
Name txtDayCount
Visible No

Then in your group footer, add a text box with Control Source of:
=[txtDayCount]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Akrt48 said:
I have a report that lists how many times a day a driver delivers another
load, I would like to count the number of days in the report it could be
three days etc... but the date is listed multiple times due to the number
of
loads. Can I do this on my report with a distinct.date query?
Click to expand...
Click to expand...
 
I can make the query ok but I am not sure how to add to my record source, I
have nothing to add it to my query with as a relationship, that I can see.
 
I can actually see the count of days when I make it visible, but in my text
box for the count ( tried Max too) I get not answer and I get a parameter
value request for txtDayCount. Can't quite figure out why.

Allen Browne said:
Do you have a Group Header for each day? If not, create one (Sorting And
Grouping box in View menu), and set the section's Visible property to No if
you do not wish to see it.

Then add a text box to this section, and set these properties:
Control Source =1
Running Sum Over Group
Format General Number
Name txtDayCount
Visible No

Then in your group footer, add a text box with Control Source of:
=[txtDayCount]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Akrt48 said:
I have a report that lists how many times a day a driver delivers another
load, I would like to count the number of days in the report it could be
three days etc... but the date is listed multiple times due to the number
of
loads. Can I do this on my report with a distinct.date query?
Click to expand...
Click to expand...
 
If you query returns a single record as I suggested, you don't need to join
it to any other table.
 
In report design view, right-click the text box and choose Properties.
On the Data tab of the Properties box, set the Control Source property to:
=1

The text box will than contain the value 1 for each record. Since it is a
Running Sum text box, this actually accumulates 1 for each record, and so it
effectively acts as a record counter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Akrt48 said:
I can't seem to make it work, I am not sure what Control Source =1 means I
have tried just as is and get zeros. All the rest makes sense, what could
I
be doing wrong? Thanks

Allen Browne said:
Do you have a Group Header for each day? If not, create one (Sorting And
Grouping box in View menu), and set the section's Visible property to No
if
you do not wish to see it.

Then add a text box to this section, and set these properties:
Control Source =1
Running Sum Over Group
Format General Number
Name txtDayCount
Visible No

Then in your group footer, add a text box with Control Source of:
=[txtDayCount]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Akrt48 said:
I have a report that lists how many times a day a driver delivers
another
load, I would like to count the number of days in the report it could
be
three days etc... but the date is listed multiple times due to the
number
of
loads. Can I do this on my report with a distinct.date query?
Click to expand...
Click to expand...
Click to expand...
 
The problem with this is the report is based on entered start and end dates
that vary depending on the person who wants the reports time frames. I
really don't know how to attach my query to the report so it looks at the
dates entered. Am I being obtuse or is this difficult to do? I am not sure
how to make the one entry query to count only the distinct dates either.
 
Allen thank you I was not doing the =txtdaycount in the footer properly,
works like a charm.

Allen Browne said:
Do you have a Group Header for each day? If not, create one (Sorting And
Grouping box in View menu), and set the section's Visible property to No if
you do not wish to see it.

Then add a text box to this section, and set these properties:
Control Source =1
Running Sum Over Group
Format General Number
Name txtDayCount
Visible No

Then in your group footer, add a text box with Control Source of:
=[txtDayCount]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Akrt48 said:
I have a report that lists how many times a day a driver delivers another
load, I would like to count the number of days in the report it could be
three days etc... but the date is listed multiple times due to the number
of
loads. Can I do this on my report with a distinct.date query?
Click to expand...
Click to expand...
 
You kick your development up a notch and don't use parameter prompt queries.
Always use controls on forms for user input of criteria. This allows the
values in the form to be used in multiple queries without having to be
prompted for values.
 
Back
Top