Multiple Critteria in Reports Form

M

Mahbub

I have created a form with 6 combo box (for example, City, Country, Ratings, Segments etc.) I want that, if I select one or more combo box, report will show only that (for example I may want to see all the Medical company in Saudi Arabia but those only High productive. If don’t select anything it should preview all the records.

Please give me a solution

Mahbu
KSA
 
J

Jim/Chris

I got this from a previous post. Hope it helps

If the RowSourceType of the control is a "Table/Query",
there are two ways of doing this. One requires the use of
an Union query, and the other one requires a callback
function to fill the control. Generally using an Union
query is easier. Callback functions are important in
certain cases, but perhaps an overkill in this situation.
For example, if your combo's RowSource is this SQL statement

SELECT CustomerID, CompanyName FROM Customers ORDER BY
CustomerID; you can then easily add "(All)" as the first
choice. Also, if CustomerID is the bound field but it's
width is set to zero (so that the user only sees
CompanyName), you can store a NULL (if the bound field is
not the primary key of the table), or someother value in
the bound field.SELECT CustomerID, CompanyName FROM
Customers UNION Select Null as AllChoice , "(All)" as Bogus
From Customers ORDER BY CustomerID; If the RowSourceType
is set to "Value List", you can simply concatenate "(All)"
as the first choice when the form opens. So if the
RowSource of the control Combo0 is "Hello"; "World"

Then this code will change it to

"(All)";"Hello"; "World"

'******* Code Start ********
Private Sub Form_Open(Cancel As Integer)
With Me.Combo0
..RowSourceType = "Value List"
..RowSource = "(All);" & .RowSource

End With
End Sub

Jim
-----Original Message-----
I have created a form with 6 combo box (for example, City,
Click to expand...
Country, Ratings, Segments etc.) I want that, if I select
one or more combo box, report will show only that (for
example I may want to see all the Medical company in Saudi
Arabia but those only High productive. If donâ?Tt select
anything it should preview all the records.
 
D

Duane Hookom

I would have a button on the form that would open the report. The code for
the button would be like:

Dim strWhere as String
Dim strReport as String
strReport = "rptYourReport"
strWhere = "1=1 "
If Not IsNull(Me.cboCity) Then
strWhere = strWhere & " AND [City] = """ & Me.cboCity & """ "
End If
If Not IsNull(Me.cboCountry) Then
strWhere = strWhere & " AND [Country] = """ & Me.cboCountry & """ "
End If
'...etc for other controls
DoCmd.OpenReport strReport, acPreview, , strWhere

--
Duane Hookom
MS Access MVP
--

Mahbub said:
I have created a form with 6 combo box (for example, City, Country,
Click to expand...
Ratings, Segments etc.) I want that, if I select one or more combo box,
report will show only that (for example I may want to see all the Medical
company in Saudi Arabia but those only High productive. If don't select
anything it should preview all the records.
 
M

Marshall Barton

Mahbub said:
I have created a form with 6 combo box (for example, City, Country, Ratings, Segments etc.) I want that, if I select one or more combo box, report will show only that (for example I may want to see all the Medical company in Saudi Arabia but those only High productive. If don’t select anything it should preview all the records.
Click to expand...


How are you currently filtering the report?

If you use the OpenReport method's WhereCondition argument,
then you can construct the filter string and just skip
adding a condition when the condition's combo box has not
been used.
 
Top