IIf() Statement in Query Criteria

I

Isbjornen

Hello,

First I want to thank Tom Ellison and John Vinson for
helping me out yesterday. Now that's done, here's my
question, similar to my question yesterday:

In the Criteria for my query I have the following
statement:

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

If [fraActive] is 3 or 4, it shows "Bonded"
or "Unassigned", otherwise it shows everything.

Now, if [fraActive] is not 3 or 4, I want the query to
show everything, but not "Bonded" and "Unassigned".

Something like this:
Like IIf([Forms]![frmStampsMain]![fraActive]=3,"Bonded",IIf
([Forms]![frmStampsMain]![fraActive]=4,"Unassigned",' Show
all "*" but not "Bonded" or "Unassigned" '))

Is it possible?

Isbjornen
 
K

Ken Snell

Try this:

Like IIf([Forms]![frmStampsMain]![fraActive]=3,"Bonded","") Or Like
IIf([Forms]![frmStampsMain]![fraActive]=4,"Unassigned","") Or ((Not Like
IIf([Forms]![frmStampsMain]![fraActive]<>3 And
[Forms]![frmStampsMain]![fraActive]<>4,"Bonded",Null) and (Not Like
IIf([Forms]![frmStampsMain]![fraActive]<>3 And
[Forms]![frmStampsMain]![fraActive]<>4,"Unassigned",Null))
 
S

Steve Schapel

Or...

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

- Steve Schapel, Microsoft Access MVP
 
K

Ken Snell

Ack! That is what I was trying to remember in my brain! Much simpler! :)

--
Ken Snell
<MS ACCESS MVP>

Steve Schapel said:
Or...

IIf([Forms]![frmStampsMain]![fraActive]=3,"Bonded",IIf([Forms]![frmStampsMai
n]![fraActive]=4,"Unassigned",Not
In("Bonded","Unassigned")))

- Steve Schapel, Microsoft Access MVP


Try this:

Like IIf([Forms]![frmStampsMain]![fraActive]=3,"Bonded","") Or Like
IIf([Forms]![frmStampsMain]![fraActive]=4,"Unassigned","") Or ((Not Like
IIf([Forms]![frmStampsMain]![fraActive]<>3 And
[Forms]![frmStampsMain]![fraActive]<>4,"Bonded",Null) and (Not Like
IIf([Forms]![frmStampsMain]![fraActive]<>3 And
[Forms]![frmStampsMain]![fraActive]<>4,"Unassigned",Null))
Click to expand...
Click to expand...
 
I

Isbjornen

Thank You Ken and Steve

Isbjornen

-----Original Message-----
Ack! That is what I was trying to remember in my brain! Much simpler! :)

--
Ken Snell
<MS ACCESS MVP>

Steve Schapel said:
Or...

IIf([Forms]![frmStampsMain]![fraActive]=3,"Bonded",IIf ([Forms]![frmStampsMai
n]![fraActive]=4,"Unassigned",Not
In("Bonded","Unassigned")))

- Steve Schapel, Microsoft Access MVP


Try this:

Like IIf([Forms]![frmStampsMain]![fraActive] =3,"Bonded","") Or Like
IIf([Forms]![frmStampsMain]![fraActive] =4,"Unassigned","") Or ((Not Like
IIf([Forms]![frmStampsMain]![fraActive]<>3 And
[Forms]![frmStampsMain]![fraActive]<>4,"Bonded",Null) and (Not Like
IIf([Forms]![frmStampsMain]![fraActive]<>3 And
[Forms]![frmStampsMain]![fraActive]
Click to expand...
Click to expand...

.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Wildcard for IIf() Statement in Query 2
Need help sorting a self join query 4
"Query too complex" workaround 4
Building bonds amortization repayment plan in Access 0
IIF stmt in Criteria 3
IIF statement in query criteria, help! 3
Date comparison in IIF statement in a query 1
IIf statement in query criteria 5

Top