Calling Mutiple cbo's from form for report

[CoachPhil]

Hi all,

Not sure how to word this (part of the problem), I have a form with 3 comboboxes- 2 for dates and 1 for employee name.
I need all three items to call the report correctly, a report on a specific employee for a specified time period. I am callimg from a query and all the info is available. So far I have

Private Sub cmdOpDateRange_Click()

DoCmd.OpenReport "AgentDetailReport2", acViewPreview, , "[LastName]='" & Me.Combo38 & "'""[date] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"

End Sub

I have been puttering away at this off and on for some time. I am certain that the answer is directly in front of me but I cannot wrap my mind around it.
Any ideas?

CoachPhil

 

[John Big Booty]

Start by writing a query that uses the three combo boxes as criteria, then use that query as the data source for your report.

 

[CameronM]

You need to add AND into you WHERE clause in the Docmd.OpenReport method to make a valid querystring.

DoCmd.OpenReport "AgentDetailReport2", acViewPreview, , "[LastName]='" & Me.Combo38 & "' AND [date] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"

As John Big Booty suggested, it is a good idea to create a query, even if only to test that your querystring is valid.

 

[DevastatioN]

Building a query would be a good idea to get your query string, however you're gonna want something roughly like this.

Dim stDocName As String
Dim strWhere As String

strWhere = "[tbl].[Date1]>= #" & [Forms]![frm]![txtStartDate] & "# And [tblOccHealth].[DRequest]<= #" & [Forms]![frm]![txtEndDate] & "# And EmployeeID = '" & txtEmployeeID & "'"

If IsNull(txtStartDate) Or IsNull(txtEndDate) Or IsNull(EmployeeID) Then
MsgBox "Please fill in all of the criteria fields to continue"
Else
stDocName = "rptWhatever"
DoCmd.OpenReport stDocName, acPreview, , strWhere
End If

Hope this helps somewhat.

 

[CoachPhil]

Gents,

Worked like a charm, thanks to all.

CoachPhil