 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / January 2006Count dates
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]  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. >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? 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 > 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 [quoted text clipped - 15 lines] > > of > > loads. Can I do this on my report with a distinct.date query? 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. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. >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 [quoted text clipped - 24 lines] >> > of >> > loads. Can I do this on my report with a distinct.date query? 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. > 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 [quoted text clipped - 15 lines] > > of > > loads. Can I do this on my report with a distinct.date query? Allen thank you I was not doing the =txtdaycount in the footer properly, works like a charm. > 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 [quoted text clipped - 15 lines] > > of > > loads. Can I do this on my report with a distinct.date query? 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. SignatureDuane Hookom MS Access MVP -- >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? 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. > 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 [quoted text clipped - 5 lines] > > of > > loads. Can I do this on my report with a distinct.date query? If you query returns a single record as I suggested, you don't need to join it to any other table. SignatureDuane Hookom MS Access MVP -- >I can make the query ok but I am not sure how to add to my record source, >I [quoted text clipped - 12 lines] >> > of >> > loads. Can I do this on my report with a distinct.date query? 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. > If you query returns a single record as I suggested, you don't need to join > it to any other table. [quoted text clipped - 15 lines] > >> > of > >> > loads. Can I do this on my report with a distinct.date query? 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. SignatureDuane Hookom MS Access MVP -- > The problem with this is the report is based on entered start and end > dates [quoted text clipped - 29 lines] >> >> > of >> >> > loads. Can I do this on my report with a distinct.date query? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.