Creating A Date Specific Report 2

[JRA]

Hi,
I've set up my table with an auto number field and now from my form I am trying to create a report that adds up numbers from a specific column that have the same serial numbers in another column.

I am wanting to do this so that it is date specific though -such that the user selects a start date and an end date for the reports. This would be dependant on a auto date column that I also have set up in the table and form.

How do I do this?

Thanks in advance,
James.

 

[Bry12345]

Here's the theory . . if you need help with the actual code, let me know. To start, you need to design a query that will return the rows relevant to your report. To do this, you prompt for a beginning and ending date (the values of which are passed to variables) and then run the query which returns the rows between the beginning and ending dates.

Then design a report on this query. Group the values by serial number. In the group header or footer, you can then add an unbound control that will total (using SUM) the numbers in the other column.

To start, I'd put the serial number and number fields in the detail area just to make sure the totals are being computer correctly. After that, if you just want totals, delete the fields in the detail section, leaving a a control for serial number and SUM in the header or footer.

HTH!

 

[JRA]

Hi ...

This sounds like a good plan HTH (Bry12345). Thank you very much. I am a beginner programmer though, so I will need help with the coding.

So far I've designed a query from my table with the column headings:
'Auto Number', 'Date', 'Serial Number', 'Page Count,, 'Company' and 'Location'.

What I want to do now is set up my report so that when the user clicks 'Print Report' on the form, it will print a report that will produce a report similiar to my diagram shown below.

I hope you can help me out. I don't have nay idea how to set this up.

Thanks,
James.

Serial Number Date Page Count Total Page Count
--------------------------------------------------------------------------------------
12345 03/12/2002 5
03/13/2002 2 7
03/14/2002 5 12
03/15/2002 8 20

67890 03/12/2002 2
03/14/2002 3 5
03/16/2002 5 10

Total Page Count 30

 

[JRA]

Hi ...

This sounds like a good plan HTH (Bry12345). Thank you very much. I am a beginner programmer though, so I will need help with the coding.

So far I've designed a query from my table with the column headings:
'Auto Number', 'Date', 'Serial Number', 'Page Count,, 'Company' and 'Location'.

What I want to do now is set up my report so that when the user clicks 'Print Report' on the form, it will print a report that will produce a report similiar to my diagram shown below.

I hope you can help me out. I don't have nay idea how to set this up.

Thanks,
James.

Serial Number Date Page Count Total Page Count
--------------------------------------------------------------------------------------
12345 03/12/2002 5
03/13/2002 2 7
03/14/2002 5 12
03/15/2002 8 20

67890 03/12/2002 2
03/14/2002 3 5
03/16/2002 5 10

Total Page Count 30

 

[Bry12345]

Start by designing a small form which will take the input for the date ranges. The form should contain two unbound boxes labelled Beginning Date and Ending Date, format: Short Date, Name: dtmBeginningDate and dtmEndingDate. The form should also contain two command buttons, Preview and Cancel, Name: cmdPreviewReport and cmdCancelForm.

Here is the code for cmdPreviewReport

- - - -
Private Sub cmdPreviewReport_Click()

'Define a variable to hold the report name
Dim strDocName As String

'Call the code below
Call PreviewReport("rptYourReport&quot
- - - -
Private Sub PreviewReport(strDocName)

' Preview report.
On Error GoTo Err_PreviewReport

'Check for valid dates and
'to see that ending date is later than beginning date.

If IsDate(dtmEndingDate) And IsDate(dtmBeginningDate) Then
If [DtmEndingDate] < [dtmBeginningDate] Then
MsgBox &quot;The ending date must be later than the beginning date.&quot;

Exit Sub
End If

Else
MsgBox &quot;Please use a valid date for the beginning date and the ending date values.&quot;

Exit Sub

End If

'Dates are OK, Open the report with those values
DoCmd.OpenReport strDocName, acViewPreview

Exit_PreviewReport:
Exit Sub

'Code for Err_PreviewReport goes here


End Sub


- - - - -
to be continued . . .

 

[Bry12345]

The query:

The query on which your report is based should contain this criteria in the date field:

Between [Forms]![frmYourInputForm]![dtmBeginningDate] And [Forms]![frmYourInputForm]![dtmEndingDate]

What this does is take the values that are entered into the two unbound boxes in the form you designed and use them to limit the records returned in the query. If this is working correctly, your report should only return records between the two dates entered in the form.

Once this is working, you can concentrate on formatting the report properly. Make sure that the report is getting the correct data first and let me know.

Bry

 

[LMRollins]

Bry12345,

This solution works great but can it be modified to print two additional reports that would use the same date range? I want to be able to create a date range form that when they click the button it would automatically print Report1, Report 2, and Report 3 using the same date range.

 

[Bry12345]

Just add additional lines after the DoCmd.OpenReport line to refer to the additional reports. The Query on which the additional reports are based must have the same 'Between' criteria in the date field.

Make sure that your input box is not closed prior to the other reports opening, or the variables will not be passed properly. - - - -

Bryan

 

[LMRollins]

Bry12345, Thanks. That worked great and that was my problem. I had it closing the form before the next report. Thank you so much.