Show all records

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have a form that has a text box for [StartDate], [EndDate] and
[Department]. I use this to print a report [PayrollReport]. This works
fine to print reports by Department. I would also like to print a
report for a date range for all Departments. The report is generated
from a query. How can this be done?
 
Mike said:
I have a form that has a text box for [StartDate], [EndDate] and
[Department]. I use this to print a report [PayrollReport]. This works
fine to print reports by Department. I would also like to print a
report for a date range for all Departments. The report is generated
from a query. How can this be done?
Click to expand...

It depends on how your report is set up to get its criteria from the
form. If your report is based on a query that uses the form's controls
as criteria, along these lines:

SELECT * FROM PayrollTable WHERE
(PayDate Between [Forms]![frmRptSelect]![StartDate]
And [Forms]![frmRptSelect]![EndDate])
AND
(Department = [Forms]![frmRptSelect]![Department]);

then you could modify it to allow for the possibility that the
[Department] control is Null:

SELECT * FROM PayrollTable WHERE
(PayDate Between [Forms]![frmRptSelect]![StartDate]
And [Forms]![frmRptSelect]![EndDate])
AND
((Department = [Forms]![frmRptSelect]![Department])
OR
([Forms]![frmRptSelect]![Department] Is Null));

An alternative way of doing this is to base your report on a query that
returns all records, but to construct and apply a filter in the code
that opens the report. Supposing that the code is attached to a command
button on the form that is supplying the criteria, then it might look
something like this:

'----- start of sample code -----
Dim varCriteria As Variant

varCriteria = Null

Select Case True
Case IsNull(Me!StartDate)
If Not IsNull(Me!EndDate) Then
varCriteria = "(PayDate <= #" & _
Format(Me!EndDate, "mm/dd/yyyy") & "#)"
End If
Case IsNull(Me!EndDate)
If Not IsNull(Me!StartDate) Then
varCriteria = "(PayDate >= #" & _
Format(Me!StartDate, "mm/dd/yyyy") & "#)"
End If
Case Else
varCriteria = "(PayDate Between #" & _
Format(Me!StartDate, "mm/dd/yyyy") & _
"# And #" & _
Format(Me!EndDate, "mm/dd/yyyy") & "#)"
End Select

If Not IsNull(Me!Department) Then
varCriteria = (varCriteria + " AND ") & _
"Department = '" & Me!Department & "'"
End If

DoCmd.OpenReport "PayrollReport", _
WhereCondition:=varCriteria & ""

'----- end of sample code -----

Notes:

1. The above code assumes that the Department field is text, not
numeric.

2. The code takes advantage of the fact that (Null+anything) yields
Null, while (Null & "x") yields "x".
 
Back
Top