Filter by form

[teel73]

I am trying to add criteria to my query using what the user selects from a form. That's easy. Basically, on the criteria line in the query grid I have the following criteria under the [Vendor] field,

[Forms]![FrmFilterReport]![cboVendor]

. That returns all records where the vendor is equal to whatever the user selected on the form from [cboVendor] combo box. But what I can't get my query to do is to return all records if the combo box is blank. Can some help me with this?

 

[twoplustwo]

I'd stick this in the click event of the combo box and stick the WHERE clause of the SQL in an IF statement.

Then set your SQL string as the recordsource of the query/listbox whatever.

Something like this:

If sTeamMember <> "ALL" Then 'If ALL ignore WHERE statement
    sSQL = sSQL & " WHERE tblDfOwner.DF_Name = '" & sTeamMember & "'"
End If

 

[teel73]

But I have three combo boxes that will provide the criteria for the query. For example: cboVendor, cboStatus, cboBuilding. The criteria for the query is to return all records wher the vendor is equal to cboVendor and the status is equal to cboStatus and the building is equal to cboBuilding. But if the user selects a vendor from cboVendor and a status from cboStatus and leave cboBuilding null, then the query should return records where the vendor = cboVendor and status = cboStatus and all buildings. Am I making this clear? Please forgive me if I am sounding confused.

 

[twoplustwo]

Hmmm it sounds like you could use a CASE statement for each permutation??

 

[teel73]

Anyone else have any ideas? Can you use an "IIF" statement on the crtieria line --- something like?

IIf(Not IsNull([Forms]![FrmFilterReport]![cboVendor]),Not Is Null,[Forms]![FrmFilterReport]![cboVendor])

How would I write my criteria if I want to return all records if there's no selection from the combo box?

 

[Brianwarnock]

A search of the forum would have returned this, please read it.

Brian

 

[teel73]

Thanks guy!!! That's awesome. That's just what I was looking for. Thank you so much.