asked on

Query syntax on where clause Using 3 IIF

Access 2010

I have in a where clause for a field:
Fee_Charged

IIf(Like "Both*","Yes","No") or IIf(Like "Yes*","Yes","No") or IIf(Like "No*","Yes","No")

I want records to be returned if the Value "Both" or "Yes" or "No"

If it sees those Values return "Yes" otherwise "No"

Thanks
fordraiders

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

the wild card is for people who may add additions to criteria...like.

"Both-Sent"
"Yes-Sent"
"Yes-Later"
etc...

Dale Fye

Ok, so this field is free text entry. Any chance the users would simply type "Y" or "N"?

Free text entry are hard to set defined criteria for, but I would generally create a small function for this.
1. I'm still not sure why you would want to return a "Yes" if the value is like "No*".
2. I'm also a bit confused, you start out indicating this is in a where clause for the [Fee Charged]. So, are you interested in adding a computed column to your query, or simply filtering on the [Fee Charged] field?

Norie

Fordraiders

Each of the Iifs in your original expression will return a string and the overall result will be a type mismatch error.

Fordraiders

ASKER

Thanks..ended up using below...thanks for helping.

Service_Charge: IIf([Fee-Implemented] Like "Both*" Or [Fee-Implemented] Like "Yes*","Yes","No")