Complex IF Statement

G

Guest

I could use some help with an IF statement I'm trying to develop. I've
started with the first half, but not sure I'm on the right path. Could
someone please help me out? I have some Columns labeled:

Ops Centers North America Billto's APAC Billto's
EMEA Billto's

What I need done is if the Ops Center is equal to North America AND the
North America billto field IS EMPTY (NULL) put a "NO" otherwise put a "YES".
However, I need added that if Ops Center is equal to Global AND North America
Billto's AND APAC Billto's AND EMEA Billto's IS EMPTY (NULL) put a "No"
otherwise "Yes".


So I decided just to try to get the North America piece working, but it's
pulling in "yes" all the time. Since I can't figure this part out I so no
point in trying the second half.

Pricing: IIf([Ops Centers]="North America" And [North America Billto's] Is
Null,"No","Yes")

Thanks in advance!!!
 
G

Guest

Thanks Lynn,
That was very helpful - just had the IsNull value misplaced. So would I
mirror that statement by joining it with "Or" to work for global. For
example, I tried two variations of an IF statement below, but the value shows
a -1 when I run the query.

Pricing: IIf([Ops Centers]="North America" And IsNull([North America
Billto's]),"No","Yes") Or IIf([Ops Centers]="Global" And IsNull([APAC
Billto's]) Or [North America Billto's] Or [EMEA Billto's],"No","Yes")

Pricing: IIf([Ops Centers]="North America" And IsNull([North America
Billto's]),"No","Yes") Or IIf([Ops Centers]="Global" And IsNull([APAC
Billto's]) Or IsNull([North America Billto's]) Or IsNull([EMEA
Billto's]),"No","Yes")

Lynn Trapp said:
Try this instead.

Pricing: IIf([Ops Centers]="North America" And IsNull([North America
Billto's]),"No","Yes")


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



jenniferspnc said:
I could use some help with an IF statement I'm trying to develop. I've
started with the first half, but not sure I'm on the right path. Could
someone please help me out? I have some Columns labeled:

Ops Centers North America Billto's APAC Billto's
EMEA Billto's

What I need done is if the Ops Center is equal to North America AND the
North America billto field IS EMPTY (NULL) put a "NO" otherwise put a
"YES".
However, I need added that if Ops Center is equal to Global AND North
America
Billto's AND APAC Billto's AND EMEA Billto's IS EMPTY (NULL) put a "No"
otherwise "Yes".


So I decided just to try to get the North America piece working, but it's
pulling in "yes" all the time. Since I can't figure this part out I so no
point in trying the second half.

Pricing: IIf([Ops Centers]="North America" And [North America Billto's] Is
Null,"No","Yes")

Thanks in advance!!!
Click to expand...
Click to expand...
 
J

John Spencer

It is possible that the field is not null but a zero-length string or a
bunch of spaces. To account for all of those try the following

Pricing: IIf([Ops Centers]="North America" And Len(Trim([North America
Billto's] & "")) > 0,"No","Yes")

You could run some tests just to see what is actually in North America
Billto's?

For example:
Field: IsNull([North America Billto's]) You should see some true (-1) if
the field is ever null
Field: Len([North America Billto's] & "") You should see zero if the field
is null or is a zero-length string. If the length is the same for every
field, then you know that the field is storing trailing zeroes. This
usually happens when you are importing from or linking to external sources.
 
L

Lynn Trapp

Jennifer,
Actually what you do for a complex IIF statement is to nest each one inside
the other.

IIF(SomeField = FirstValue, "FirstChoice", IIF(SomeField = SecondValue,
"SecondChoice", IIF(someField = ThirdValue, "ThirdChoice", "FourthChoice")

Be careful about going too deep in an IIF statement. They can get pretty
unwieldly. Looking at your full situation you may want to look at doing an
IF or CASE statement in VBA.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



jenniferspnc said:
Thanks Lynn,
That was very helpful - just had the IsNull value misplaced. So would I
mirror that statement by joining it with "Or" to work for global. For
example, I tried two variations of an IF statement below, but the value
shows
a -1 when I run the query.

Pricing: IIf([Ops Centers]="North America" And IsNull([North America
Billto's]),"No","Yes") Or IIf([Ops Centers]="Global" And IsNull([APAC
Billto's]) Or [North America Billto's] Or [EMEA Billto's],"No","Yes")

Pricing: IIf([Ops Centers]="North America" And IsNull([North America
Billto's]),"No","Yes") Or IIf([Ops Centers]="Global" And IsNull([APAC
Billto's]) Or IsNull([North America Billto's]) Or IsNull([EMEA
Billto's]),"No","Yes")

Lynn Trapp said:
Try this instead.

Pricing: IIf([Ops Centers]="North America" And IsNull([North America
Billto's]),"No","Yes")


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



jenniferspnc said:
I could use some help with an IF statement I'm trying to develop. I've
started with the first half, but not sure I'm on the right path. Could
someone please help me out? I have some Columns labeled:

Ops Centers North America Billto's APAC Billto's
EMEA Billto's

What I need done is if the Ops Center is equal to North America AND the
North America billto field IS EMPTY (NULL) put a "NO" otherwise put a
"YES".
However, I need added that if Ops Center is equal to Global AND North
America
Billto's AND APAC Billto's AND EMEA Billto's IS EMPTY (NULL) put a "No"
otherwise "Yes".


So I decided just to try to get the North America piece working, but
it's
pulling in "yes" all the time. Since I can't figure this part out I so
no
point in trying the second half.

Pricing: IIf([Ops Centers]="North America" And [North America Billto's]
Is
Null,"No","Yes")

Thanks in advance!!!
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

if query2- urgent requried 1
If statement 4
IF THEN 4
If Column A = X then put in Column B Y 3
Daylight Saving Time 7
Compare cells and concatenate on match 2
IIF Statement in Query Field Criteria 2
Apple now worth more than Microsoft 3

Top