Help on iif() in query

  • Thread starter Thread starter Jen
  • Start date Start date
J

Jen

Hello all.

In a qyery I retrieve a value from a form. If this value is 1, 2, 3 or 4
thats what the query should stick with (and does too). But if the value is
5, then the query should retrieve the values 1 and 2. If I write "1 or 2" in
the query criteria then I get these two. If I however try to put them in an
iif() statement like:

IIf([Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status]
=5;1 Or
2;[Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status])
then I can't get it to work. Any ideas?

Jen
 
Jen

So you're saying that if the underlying field value is 5, then you want two
values returned (both 1 and 2)? Queries return one value per field.

From your description, you've asked the IIF() statement to return a boolean
value (1 Or 2). If you want it to return the phrase "1 or 2", put that in
quotes.

Good luck

Jeff Boyce
<Access MVP>
 
Dear Jen:

How about:

WHERE YourColumn =
Switch([Forms]![startform]![subform_lista]_per_tid_status].[Form]![frame_status],
1, 2, 3, 4, 1) OR
YourColumn =
Switch([Forms]![startform]![subform_lista]_per_tid_status].[Form]![frame_status],
1, 2, 3, 4, 2)

This has some advantages. If you ever want to have a value of 6
meaning 2 or 4 it is easy to add that. There is some likelihood that
this construction might then be more "extensible."

Hello all.

In a qyery I retrieve a value from a form. If this value is 1, 2, 3 or 4
thats what the query should stick with (and does too). But if the value is
5, then the query should retrieve the values 1 and 2. If I write "1 or 2" in
the query criteria then I get these two. If I however try to put them in an
iif() statement like:

IIf([Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status]
=5;1 Or
2;[Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status])
then I can't get it to work. Any ideas?

Jen
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Jeff, what I want to do is to have the query behave the same way as when I
write "1 or 2" (without "-marks) in the query's Criteria grid. This only
when the forms (from where I launch the query) optionbuttons frame's value
is 5. Otherwise the query should use whatever the optionframe's value is
(i.e 1, 2, 3, 4).

Jen.
 
Back
Top