Wildcard for IIf() Statement in Query

[Isbjornen]

Hello,

I'm trying to find a wildcard for IIF statement in a Query
criteria. Here's what it looks like in the Query Criteria:

IIf([Forms]![frmStampsMain]![fraActive]=3,"Bonded",IIf
([Forms]![frmStampsMain]![fraActive]
=4,"Unassigned", 'Something goes here for the false
statement to display all values'))

If [fraActive] is 3 or 4, it shows either "Bonded"
or "Unassigned", which is good. However, when [fraActive]
is 1 or 2, I want it to display
both "Bonded", "Unassigned", and anything else.

I've tried to use *, ?, %, and _ but I can't get it to
work.

Please help,

Isbjornen

 

[Tom Ellison]

Dear ls:

WHERE
([Forms]![frmStampsMain]![fraActive] = 3
AND SomeColumn = "Bonded")
OR ([Forms]![frmStampsMain]![fraActive] = 4
AND SomeColumn = "Unassigned")
OR [Forms]![frmStampsMain]![fraActive] NOT IN (3, 4)

WHERE SomeColumn LIKE
IIf([Froms]![frmStampsMain]![fraActive] = 3, "Bonded",
IIf([Forms]![frmStampsMain]![fraActive] = 2, "Unassigned",
"*")

You could try either of the preceding. The first is quite unlike you
logic, but I find it easier to read and easier to expand if the logic
becomes more complex. Nesting IIf beyond two levels, as required for
your current logic, is about as far as I like to see it get.

Hello,

I'm trying to find a wildcard for IIF statement in a Query
criteria. Here's what it looks like in the Query Criteria:

IIf([Forms]![frmStampsMain]![fraActive]=3,"Bonded",IIf
([Forms]![frmStampsMain]![fraActive]
=4,"Unassigned", 'Something goes here for the false
statement to display all values'))

If [fraActive] is 3 or 4, it shows either "Bonded"
or "Unassigned", which is good. However, when [fraActive]
is 1 or 2, I want it to display
both "Bonded", "Unassigned", and anything else.

I've tried to use *, ?, %, and _ but I can't get it to
work.

Please help,

Isbjornen

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Vinson]

I'm trying to find a wildcard for IIF statement in a Query
criteria. Here's what it looks like in the Query Criteria:

IIf([Forms]![frmStampsMain]![fraActive]=3,"Bonded",IIf
([Forms]![frmStampsMain]![fraActive]
=4,"Unassigned", 'Something goes here for the false
statement to display all values'))

If [fraActive] is 3 or 4, it shows either "Bonded"
or "Unassigned", which is good. However, when [fraActive]
is 1 or 2, I want it to display
both "Bonded", "Unassigned", and anything else.

Try the LIKE operator - the default equals operator does not handle
wildcards:

LIKE IIf([Forms]![frmStampsMain]![fraActive]=3,"Bonded",
IIf([Forms]![frmStampsMain]![fraActive]=4,"Unassigned","*"))