Report Parameters Problem 1

[TedRSki]

I looked through the Threads and found bits and pieces of some code for passing parameters to a report but it seems I still don't have it just right. The problem is I am using two textboxes for a Start Date and an End Date and a combo box for employee names. The report should only show the employee chosen and their days worked between the start date and the end date.

The code I have is this:


Private Sub Command21_Click()
On Error GoTo Err_Command21_Click

Dim stDocName As String
stDocName = "rpt_Emp_Wkly"

' Start Date Between #10/15/2010# And #10/16/2010#
' End Date Between #10/15/2010# And #10/16/2010#

Dim sCrit As String
If Len(Trim(Forms![frmReports]![Text0] & "")) Then
sCrit = "[SDate] Between #" _
& Forms![frmReports]![Text0] & "#"
End If
If Len(Trim(Forms![frmReports]![Text1] & "")) Then
If Len(sCrit) Then sCrit = sCrit & " And"
sCrit = sCrit & " #" _
& Forms![frmReports]![Text1] & "#"
End If
If Len(Trim(Forms![frmReports]![Text1] & "")) Then
sCrit = sCrit & " And [EDate] Between #" _
& Forms![frmReports]![Text0] & "#"
End If
If Len(Trim(Forms![frmReports]![Text1] & "")) Then
If Len(sCrit) Then sCrit = sCrit & " And"
sCrit = sCrit & " #" _
& Forms![frmReports]![Text1] & "#"
End If

' Employee Name = Alvarez, Juan

If Len(Trim(Forms![frmReports]![Combo19] & "")) Then
If Len(sCrit) Then sCrit = sCrit & " And"
sCrit = sCrit _
& " [EMPNAME] = '" _
& Forms![frmReports]![Combo19] & "'"
End If

DoCmd.OpenReport stDocName, acPreview, sCrit

Exit_Command21_Click:
Exit Sub

Err_Command21_Click:
MsgBox Err.Description
Resume Exit_Command21_Click
End Sub

 

[PHV]

DoCmd.OpenReport stDocName, acViewPreview, [!],[/!] sCrit

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TedRSki]

PHV, Thank you very much for the quick and correct response. It has helped a lot. With the sCrit field for criteria, the report request is now working great. I can add as many or as few filters as I want. Thank you again.