Query Filter Trough ComboBox

[Dinjyo65]

Hi All,
I am pretty new to Access. I am using Access 2010.

I am trying to run a query to filter data based on combobox. It's seems to be drawing blank without pulling any results.
Detailed explanation:


I have a table with lot's of column and i need to filter this table based on a value selected by user. Only three values can be selected by a user and i am using this to filter one of the columns using IFF function.

IIf([Forms]![Test_Form]![Combo97]="Only Pass Results","L",IIf([Forms]![Test_Form]![Combo97]="Only Fail Results","P","*"))

- It seems to be drawing blank.
- Secondly if i just use one IFF statement, "*" doesn't seem to work.

Any help appreciated.
Thanks

[June7]

Wildcard needs LIKE operator. Think I have tried to set LIKE "*" in IIf criteria expression and never got it to work.

Is the combobox RowSource a ValueList or query?

Maybe you need to restructure combobox. Make it a multicolumn and bind it to the column with L/P values but set the width of that column to 0 so users only see the full descriptor. Then the criteria could be:

LIKE [Forms]![Test_Form]![Combo97] & "*"

[Dinjyo65]

Being a starter on Access, no idea what u meant.
I have attached database with query, can't figure out why its not working.

If you could have a quick look. I assume it's some sort of typo or a quick fix.
Thanks

[June7]

Why did your example in post show Combo97 but the name is actually Combo29? And the form name is actually MainForm.

The combobox is multi-column and RowSource is:
SELECT [Criteria].[ID], [Criteria].[CriteriaS] FROM Criteria ORDER BY [CriteriaS] DESC;
ID is the bound field.

The criteria in query is under the OrderType field of table Main. This field is text. The criteria from the combobox is ID which is number (1, 2, 3). The values in OrderType field are P and L. No records are retrieved because no data matches.

There is no 'quick fix'. Your data structure is wrong. There is no primary key/foreign key relationship between these two tables. Data saved into table Main does not relate the the records in table Criteria.

[Dinjyo65]

June7 - Sorry for confusion. Basically i quickly created a temp database based on actual database to explain the problem.

I have made correction based on your reply and it seems to be working except, when i select "All" i don't want to filter it and hence i want it to display all results.


Step1: User selects Pass / Fail / All on form.
Step2: Query filters based Pass/Fail. I want this to show all data when "All" selected.

Any suggestion where i am going wrong
Thanks

[June7]

As stated, never been able to get IIf with wildcard to work in query parameter. Still no luck with your latest version of db.

What you can do is have only the Pass and Fail options then if the combobox is left empty, all records will return.

However, I would not open query directly. I would open a form bound to the query. Actually, I don't use the query input parameter method. I use code to build the criteria string then:

1. use WHERE CONDITION argument
Private Sub Combo31_AfterUpdate()
DoCmd.OpenForm "formname", , , "Criteria LIKE '" & IIf(Me.Combo31 = "All", "", Me.Combo31) & "*'"
End Sub

or

2. bind MainForm to the query, put the combobox in form header section, set form as Continuous View, set Filter and FilterOn properties
Private Sub Combo31_AfterUpdate()
Me.FilterOn = False
Me.Filter = "Criteria LIKE '" & IIf(Me.Combo31 = "All", "", Me.Combo31) & "*'"
Me.FilterOn = True
End Sub