IIF in form query

[Only4Access]

Hello Everyone,

I am newbie in Access. This will be very simple for you guys, but for me it's not.

I have a table, tbl1. I am making a search form which I got through this Site

In the form there is a compo with 3 drop down, All, Pending, Closed. In tbl1 there is a field called "Status". So basically I am trying to retrieve data based on comp selection. In Query I tried under "Status" field 'Criteria' area,

IIF([Forms]![Search]![SearchStatus]="All",Like "*",[Forms]![Search]![SearchStatus])

If I select 'All' nothing pulled, but for 'Pending' & 'Closed' works.

How can I fix this. If I select 'All' pull everything <>"" in "Status" field.



Thank you for your time.

Raj

[June7]

Think I have encountered this before. Can't get the wildcard to work in IIf criteria. Try this:

1. Create field in query with expression:
CriteriaMatch: IIf("All"=[Forms]![Search]![SearchStatus] Or [Status]=[Forms]![Search]![SearchStatus],True,False)

2. Criteria under this constructed field: True

If you want to exclude records that don't have any status value (assuming the field will be Null, not empty string - I don't allow empty strings in fields):
CriteriaMatch: IIf(("All"=[Forms]![Search]![SearchStatus] Or [Status]=[Forms]![Search]![SearchStatus]) AND Not IsNull([Status]),True,False)

[alansidman]

June beat me. Go with her solution

Alan

[Only4Access]

Thank you Jun & Alan, works great.