Results 1 to 2 of 2
2008-01-09, 22:19 #1
- Join Date
- Jun 2003
- Camp Lejeune, North Carolina, USA
- Thanked 0 Times in 0 Posts
Report form totaling fields within certain dates (2003)
Ok Here we go. I need to create a report form from an existing database to total the number of entries for each of the 6 category names (mission, personal, medical, legal, career change, and other. This has to be able to be done for a specific date range i.e. 2008-01-07 thru 2008-01-10. I am mostly interested on the correct syntex for each and how to create the form with dates being pulled at the top of the report
2008-01-09, 22:40 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Report form totaling fields within certain dates (2003)
You can specify a grouping level for the report in the Sorting and Grouping dialog. To group on a field, you must select it in the Field/Expression column, and set either Group Header or Group Footer or both to Yes.
In the group header or footer section, you can use expressions such as =Count(*) to display the number of records in that group.
If you create a form with text boxes txtStartDate and txtEndDate, you can open the report from the form (for example in the On Click event of a command button). Code looks like this:
DoCmd.OpenReport "rptMyReport", acViewPreview, , "[DateField] Between #" & _
Format(Me.txtStartDate, "mm/dd/yyyy") & "# And #" & Format(Me.txtEndDate, "mm/dd/yyyy") & "#"
Substitute the actual names.
You can place a text box on the form with control source
and another one for the end date. Or a text box with a control source like this:
="From " & Format(Me.txtStartDate, "yyyy-mm-dd") & " to " & Format(Me.txtEndDate, "yyyy-mm-dd")</code>