Report based on multiple combo boxes

S

sexton75

I am trying to base a report on multiple selections from combo boxes. The
way I have it set up right now will only send back the results if a selection
is made on all combo boxes. Ideally, I would like to have multiple options
for the user to select from and based on only those selections filter the
results of the report. The code I have right now is as follows:

Private Sub ReportPreview_Click()
Dim strWhere As String
strWhere = "True"
If Not IsNull(Me!RptCat) Then
strWhere = strWhere & " AND [cat1id]= " & Me!RptCat & " AND [cat2id]= " & Me!
rptcat2 & " AND [cat3id]= " & Me!rptcat3
DoCmd.OpenReport "Rpt_AssetValuebytype", acViewPreview, , strWhere
End If
End Sub

Please help
 
J

John Spencer

Try splitting up things a little

Private Sub ReportPreview_Click()
Dim strWhere As String

strWhere = "True"

If Not IsNull(Me!RptCat) Then
strWhere = strWhere & " AND [cat1id]= " & Me!RptCat
end if

If not IsNull(Me.RptCat2) Then
strWhere = strWhere & " AND [cat2id]= " & Me!rptcat2
END IF

If Not IsNull(Me.RptCat3) Then
strWhere = strWhere & " AND [cat3id]= " & Me!rptcat3
END IF


DoCmd.OpenReport "Rpt_AssetValuebytype", acViewPreview, , strWhere

End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
S

sexton75

That worked great! Thanks so much for your help!

John said:
Try splitting up things a little

Private Sub ReportPreview_Click()
Dim strWhere As String

strWhere = "True"

If Not IsNull(Me!RptCat) Then
strWhere = strWhere & " AND [cat1id]= " & Me!RptCat
end if

If not IsNull(Me.RptCat2) Then
strWhere = strWhere & " AND [cat2id]= " & Me!rptcat2
END IF

If Not IsNull(Me.RptCat3) Then
strWhere = strWhere & " AND [cat3id]= " & Me!rptcat3
END IF

DoCmd.OpenReport "Rpt_AssetValuebytype", acViewPreview, , strWhere

End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
I am trying to base a report on multiple selections from combo boxes. The
way I have it set up right now will only send back the results if a selection
Click to expand...
[quoted text clipped - 13 lines]
Please help
Click to expand...
Click to expand...
 
Top