IIF Statement

G

Guest

Can some one look at this and let me know why it isnot working??

The filed, ([sweet_spot_analysis].[CurrentZone], is a field in the query
that is prompted. Based on this value, I wanted the following to occur (see
below)


iif([sweet_spot_analysis].[CurrentZone] = ‘zonef’,
iif([sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneAmin] and
[ZoneMinMAx].[zoneAmax], “MoveToZoneAâ€, iif([sweet_spot_analysis].[8WkIC]
between [ZoneMinMAx].[zoneBmin] and [ZoneMinMAx].[zoneBmax], “MoveToZoneBâ€,
iif([sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneCmin] and
[ZoneMinMAx].[zoneCmax], “MoveToZoneCâ€, iif([sweet_spot_analysis].[8WkIC]
between [ZoneMinMAx].[zoneJmin] and [ZoneMinMAx].[zoneJmax], “MoveToZoneJâ€,
“continuecodeâ€))))) as ZoneEnd

Thanks
 
B

babyatx13 via AccessMonster.com

Sean said:
Can some one look at this and let me know why it isnot working??

The filed, ([sweet_spot_analysis].[CurrentZone], is a field in the query
that is prompted. Based on this value, I wanted the following to occur (see
below)

iif([sweet_spot_analysis].[CurrentZone] = ‘zonef’,
iif([sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneAmin] and
[ZoneMinMAx].[zoneAmax], “MoveToZoneAâ€, iif([sweet_spot_analysis].[8WkIC]
between [ZoneMinMAx].[zoneBmin] and [ZoneMinMAx].[zoneBmax], “MoveToZoneBâ€,
iif([sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneCmin] and
[ZoneMinMAx].[zoneCmax], “MoveToZoneCâ€, iif([sweet_spot_analysis].[8WkIC]
between [ZoneMinMAx].[zoneJmin] and [ZoneMinMAx].[zoneJmax], “MoveToZoneJâ€,
“continuecodeâ€))))) as ZoneEnd

Thanks
Click to expand...

you have a bunch of "if"s but no "then"s

K Board
 
B

babyatx13 via AccessMonster.com

Sean said:
Can some one look at this and let me know why it isnot working??

The filed, ([sweet_spot_analysis].[CurrentZone], is a field in the query
that is prompted. Based on this value, I wanted the following to occur (see
below)

iif([sweet_spot_analysis].[CurrentZone] = ‘zonef’,
iif([sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneAmin] and
[ZoneMinMAx].[zoneAmax], “MoveToZoneAâ€, iif([sweet_spot_analysis].[8WkIC]
between [ZoneMinMAx].[zoneBmin] and [ZoneMinMAx].[zoneBmax], “MoveToZoneBâ€,
iif([sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneCmin] and
[ZoneMinMAx].[zoneCmax], “MoveToZoneCâ€, iif([sweet_spot_analysis].[8WkIC]
between [ZoneMinMAx].[zoneJmin] and [ZoneMinMAx].[zoneJmax], “MoveToZoneJâ€,
“continuecodeâ€))))) as ZoneEnd

Thanks
Click to expand...

I’m not entirely sure what your fields represent but from the looks of it you
have a bunch of "if"s but no "then"s, you are in fact nesting your if's you
need a "then" between them

iif([sweet_spot_analysis].[CurrentZone] = ‘zonef’ then
iif([sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneAmin] and
[ZoneMinMAx].[zoneAmax] then
etc….
end if
end if

K Board
 
G

Guest

I would never nest IIf()s more than one deep. I would prefer a user-defined
function that would be much easier to maintain. It also looks like you might
have a normalization issue but I'm not sure.

You have single quotes around 'zonef' but double quotes around other
hard-coded values. Is there a reason?
 
J

John W. Vinson

you have a bunch of "if"s but no "then"s

K Board
Click to expand...

I think you're thinking of If statements in VBA code, rather than the
IIf (Immediate If) built-in function.

John W. Vinson [MVP]
 
J

John Spencer

If I had to do this in a query, I think I would try using the switch
function as it is a bit easier to understand then those nested IIF
statements. If I misunderstood some of your logic, you can clean it up
by adding in the additional tests in each condition-response pair below.

Switch([sweet_spot_analysis].[CurrentZone] = "zonef" AND
[sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneAmin] and
[ZoneMinMAx].[zoneAmax], “MoveToZoneAâ€

,[sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneBmin] and
[ZoneMinMAx].[zoneBmax], “MoveToZoneBâ€

,[sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneCmin] and
[ZoneMinMAx].[zoneCmax], “MoveToZoneCâ€

,[sweet_spot_analysis].[8WkIC]
between [ZoneMinMAx].[zoneJmin] and [ZoneMinMAx].[zoneJmax], “MoveToZoneJâ€
, True,"Continue Code") as ZoneEnd
 
G

Guest

The query will prompt the user to enter a zone, so if 'ZoneF' is entered,
then the calculations need to take place. The text in the " " is what it
displayed if the if statments is true. I have done nested if's before but
doing one with a "between" 2 different fields is through me off. Any
suggestions?

Duane Hookom said:
I would never nest IIf()s more than one deep. I would prefer a user-defined
function that would be much easier to maintain. It also looks like you might
have a normalization issue but I'm not sure.

You have single quotes around 'zonef' but double quotes around other
hard-coded values. Is there a reason?

--
Duane Hookom
Microsoft Access MVP


Sean said:
Can some one look at this and let me know why it isnot working??

The filed, ([sweet_spot_analysis].[CurrentZone], is a field in the query
that is prompted. Based on this value, I wanted the following to occur (see
below)


iif([sweet_spot_analysis].[CurrentZone] = ‘zonef’,
iif([sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneAmin] and
[ZoneMinMAx].[zoneAmax], “MoveToZoneAâ€, iif([sweet_spot_analysis].[8WkIC]
between [ZoneMinMAx].[zoneBmin] and [ZoneMinMAx].[zoneBmax], “MoveToZoneBâ€,
iif([sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneCmin] and
[ZoneMinMAx].[zoneCmax], “MoveToZoneCâ€, iif([sweet_spot_analysis].[8WkIC]
between [ZoneMinMAx].[zoneJmin] and [ZoneMinMAx].[zoneJmax], “MoveToZoneJâ€,
“continuecodeâ€))))) as ZoneEnd

Thanks
Click to expand...
Click to expand...
 
G

Guest

This is an else iff statement, it does not need the "then" in there.

babyatx13 via AccessMonster.com said:
Sean said:
Can some one look at this and let me know why it isnot working??

The filed, ([sweet_spot_analysis].[CurrentZone], is a field in the query
that is prompted. Based on this value, I wanted the following to occur (see
below)

iif([sweet_spot_analysis].[CurrentZone] = ‘zonef’,
iif([sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneAmin] and
[ZoneMinMAx].[zoneAmax], “MoveToZoneAâ€, iif([sweet_spot_analysis].[8WkIC]
between [ZoneMinMAx].[zoneBmin] and [ZoneMinMAx].[zoneBmax], “MoveToZoneBâ€,
iif([sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneCmin] and
[ZoneMinMAx].[zoneCmax], “MoveToZoneCâ€, iif([sweet_spot_analysis].[8WkIC]
between [ZoneMinMAx].[zoneJmin] and [ZoneMinMAx].[zoneJmax], “MoveToZoneJâ€,
“continuecodeâ€))))) as ZoneEnd

Thanks
Click to expand...

you have a bunch of "if"s but no "then"s

K Board
Click to expand...
 
G

Guest

My suggestion would be to send all the field values into a user-defined
function that will return the appropriate value. A function encapsulates your
logic and calcs in one place. Create the function in a module for all
business related calculations.

--
Duane Hookom
Microsoft Access MVP


Sean said:
The query will prompt the user to enter a zone, so if 'ZoneF' is entered,
then the calculations need to take place. The text in the " " is what it
displayed if the if statments is true. I have done nested if's before but
doing one with a "between" 2 different fields is through me off. Any
suggestions?

Duane Hookom said:
I would never nest IIf()s more than one deep. I would prefer a user-defined
function that would be much easier to maintain. It also looks like you might
have a normalization issue but I'm not sure.

You have single quotes around 'zonef' but double quotes around other
hard-coded values. Is there a reason?

--
Duane Hookom
Microsoft Access MVP


Sean said:
Can some one look at this and let me know why it isnot working??

The filed, ([sweet_spot_analysis].[CurrentZone], is a field in the query
that is prompted. Based on this value, I wanted the following to occur (see
below)


iif([sweet_spot_analysis].[CurrentZone] = ‘zonef’,
iif([sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneAmin] and
[ZoneMinMAx].[zoneAmax], “MoveToZoneAâ€, iif([sweet_spot_analysis].[8WkIC]
between [ZoneMinMAx].[zoneBmin] and [ZoneMinMAx].[zoneBmax], “MoveToZoneBâ€,
iif([sweet_spot_analysis].[8WkIC] between [ZoneMinMAx].[zoneCmin] and
[ZoneMinMAx].[zoneCmax], “MoveToZoneCâ€, iif([sweet_spot_analysis].[8WkIC]
between [ZoneMinMAx].[zoneJmin] and [ZoneMinMAx].[zoneJmax], “MoveToZoneJâ€,
“continuecodeâ€))))) as ZoneEnd

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Top