"IIf" formula, how to return a value of zero

A

Alberta Rose

Can anyone help me with this? I need to know where to put the coding to tell
the formula that if there is no value in any of the txt's, to return a value
of zero.

=IIf([costcode]="013210" And [costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],
IIf([costcode]="064201" and [costtype]="05320",
[txtEstimatedCost]/[txtCraftSuprLabEst],Null)))

Thanks so much...Laurie
 
C

Clifford Bass via AccessMonster.com

Hi Laurie,

You could add another IIf() function:

IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions
here] )

Just a note that you can use the Switch() function to eliminate the need
to have all those nested IIf() functions.

=Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And
[costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype]
="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and
[costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null)

Look in your online help for details about the Switch() function.

Clifford Bass

Alberta said:
Can anyone help me with this? I need to know where to put the coding to tell
the formula that if there is no value in any of the txt's, to return a value
of zero.

=IIf([costcode]="013210" And [costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],
IIf([costcode]="064201" and [costtype]="05320",
[txtEstimatedCost]/[txtCraftSuprLabEst],Null)))

Thanks so much...Laurie
Click to expand...
 
A

Alberta Rose

Good day. I get a syntax error message when i try to use the =Switch code.
I put a [] around the first costtype word instead of (), but doesn't seem to
be happy yet.

Laurie

Clifford Bass via AccessMonster.com said:
Hi Laurie,

You could add another IIf() function:

IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions
here] )

Just a note that you can use the Switch() function to eliminate the need
to have all those nested IIf() functions.

=Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And
[costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype]
="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and
[costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null)

Look in your online help for details about the Switch() function.

Clifford Bass

Alberta said:
Can anyone help me with this? I need to know where to put the coding to tell
the formula that if there is no value in any of the txt's, to return a value
of zero.

=IIf([costcode]="013210" And [costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],
IIf([costcode]="064201" and [costtype]="05320",
[txtEstimatedCost]/[txtCraftSuprLabEst],Null)))

Thanks so much...Laurie
Click to expand...
Click to expand...
 
J

John Spencer

Switch(IsNull([costcode]) Or IsNull(costtype), 0
, [costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst]
, [costcode]="020110" and [costtype] ="05320",
[txtEstimatedCost]/[txtFirewatchLabEst]
, [costcode]="064201" and [costtype]="05320",
[txtEstimatedCost]/[txtCraftSuprLabEst]
, True, Null)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Alberta said:
Good day. I get a syntax error message when i try to use the =Switch code.
I put a [] around the first costtype word instead of (), but doesn't seem to
be happy yet.

Laurie

Clifford Bass via AccessMonster.com said:
Hi Laurie,

You could add another IIf() function:

IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions
here] )

Just a note that you can use the Switch() function to eliminate the need
to have all those nested IIf() functions.

=Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And
[costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype]
="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and
[costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null)

Look in your online help for details about the Switch() function.

Clifford Bass

Alberta said:
Can anyone help me with this? I need to know where to put the coding to tell
the formula that if there is no value in any of the txt's, to return a value
of zero.

=IIf([costcode]="013210" And [costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],
IIf([costcode]="064201" and [costtype]="05320",
[txtEstimatedCost]/[txtCraftSuprLabEst],Null)))

Thanks so much...Laurie
Click to expand...
Click to expand...
Click to expand...
 
A

Alberta Rose

OK I got this to work by putting () around [costcode] as well as [costtype].
Could I use the IsNull if the txtEstimatedCost is zero as well? Right now if
the txtEstimatedCost is zero, I get an error message #Num!, same if the
txtActualCost is zero.

Clifford Bass via AccessMonster.com said:
Hi Laurie,

You could add another IIf() function:

IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions
here] )

Just a note that you can use the Switch() function to eliminate the need
to have all those nested IIf() functions.

=Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And
[costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype]
="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and
[costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null)

Look in your online help for details about the Switch() function.

Clifford Bass

Alberta said:
Can anyone help me with this? I need to know where to put the coding to tell
the formula that if there is no value in any of the txt's, to return a value
of zero.

=IIf([costcode]="013210" And [costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],
IIf([costcode]="064201" and [costtype]="05320",
[txtEstimatedCost]/[txtCraftSuprLabEst],Null)))

Thanks so much...Laurie
Click to expand...
Click to expand...
 
J

John Spencer

Zero is a value you will get errors if txtCraftLabEst or txtFirewatchLabEst or
txtCraftSuprLabEst is equal to zero and you try to calculate a value based on
those fields when the value of the field is zero.

If txtEstimatedCost is equal to zero you will automatically get zero returned
as the result - unless the above is true for the denominator (=0) is involved.

You could use
Switch(IsNull([costcode]) Or IsNull(costtype), 0
, [costcode]="013210" And [costtype]="05320" AND txtCraftLabEst<>0,
[txtEstimatedCost]/[txtCraftLabEst]
, [costcode]="020110" and [costtype] ="05320" AND txtFirewatchLabEst<>0,
[txtEstimatedCost]/[txtFirewatchLabEst]
, [costcode]="064201" and [costtype]="05320" AND txtCraftSuprLabEst<>0,
[txtEstimatedCost]/[txtCraftSuprLabEst]
, True, Null)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Alberta said:
OK I got this to work by putting () around [costcode] as well as [costtype].
Could I use the IsNull if the txtEstimatedCost is zero as well? Right now if
the txtEstimatedCost is zero, I get an error message #Num!, same if the
txtActualCost is zero.

Clifford Bass via AccessMonster.com said:
Hi Laurie,

You could add another IIf() function:

IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions
here] )

Just a note that you can use the Switch() function to eliminate the need
to have all those nested IIf() functions.

=Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And
[costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype]
="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and
[costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null)

Look in your online help for details about the Switch() function.

Clifford Bass

Alberta said:
Can anyone help me with this? I need to know where to put the coding to tell
the formula that if there is no value in any of the txt's, to return a value
of zero.

=IIf([costcode]="013210" And [costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],
IIf([costcode]="064201" and [costtype]="05320",
[txtEstimatedCost]/[txtCraftSuprLabEst],Null)))

Thanks so much...Laurie
Click to expand...
Click to expand...
Click to expand...
 
C

Clifford Bass via AccessMonster.com

Hi Laurie,

That was due to a missing closing paranthesis, as you have discovered:

=Switch(IsNull([costcode] Or...

should have been

=Switch(IsNull([costcode]) Or...

I see John has given you the way to deal with zero values in the
denominators. Good.

Clifford Bass

Alberta said:
Good day. I get a syntax error message when i try to use the =Switch code.
I put a [] around the first costtype word instead of (), but doesn't seem to
be happy yet.

Laurie
Click to expand...
 
A

Alberta Rose

Good morning. I get an error message telling me that my expression is too
complex. Any other suggestions?

John Spencer said:
Zero is a value you will get errors if txtCraftLabEst or txtFirewatchLabEst or
txtCraftSuprLabEst is equal to zero and you try to calculate a value based on
those fields when the value of the field is zero.

If txtEstimatedCost is equal to zero you will automatically get zero returned
as the result - unless the above is true for the denominator (=0) is involved.

You could use
Switch(IsNull([costcode]) Or IsNull(costtype), 0
, [costcode]="013210" And [costtype]="05320" AND txtCraftLabEst<>0,
[txtEstimatedCost]/[txtCraftLabEst]
, [costcode]="020110" and [costtype] ="05320" AND txtFirewatchLabEst<>0,
[txtEstimatedCost]/[txtFirewatchLabEst]
, [costcode]="064201" and [costtype]="05320" AND txtCraftSuprLabEst<>0,
[txtEstimatedCost]/[txtCraftSuprLabEst]
, True, Null)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Alberta said:
OK I got this to work by putting () around [costcode] as well as [costtype].
Could I use the IsNull if the txtEstimatedCost is zero as well? Right now if
the txtEstimatedCost is zero, I get an error message #Num!, same if the
txtActualCost is zero.

Clifford Bass via AccessMonster.com said:
Hi Laurie,

You could add another IIf() function:

IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions
here] )

Just a note that you can use the Switch() function to eliminate the need
to have all those nested IIf() functions.

=Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And
[costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype]
="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and
[costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null)

Look in your online help for details about the Switch() function.

Clifford Bass

Alberta Rose wrote:
Can anyone help me with this? I need to know where to put the coding to tell
the formula that if there is no value in any of the txt's, to return a value
of zero.

=IIf([costcode]="013210" And [costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],
IIf([costcode]="064201" and [costtype]="05320",
[txtEstimatedCost]/[txtCraftSuprLabEst],Null)))

Thanks so much...Laurie
Click to expand...
Click to expand...
.
Click to expand...
 
J

John Spencer

No, I don't see any syntax error in that expression. If you remove the
expression completely do you still get the query is too complex? If so, then
the expression is not the problem.

Switch(IsNull([costcode]) Or IsNull(costtype), 0
, [costcode]="013210" And [costtype]="05320" AND txtCraftLabEst<>0
, [txtEstimatedCost]/[txtCraftLabEst]
, [costcode]="020110" and [costtype] ="05320" AND txtFirewatchLabEst<>0
, [txtEstimatedCost]/[txtFirewatchLabEst]
, [costcode]="064201" and [costtype]="05320" AND txtCraftSuprLabEst<>0
, [txtEstimatedCost]/[txtCraftSuprLabEst]
, True, Null)

Perhaps you can post the SQL text of the query that you are trying to run.
(Open query in SQL view and copy and paste the text)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Alberta said:
Good morning. I get an error message telling me that my expression is too
complex. Any other suggestions?

John Spencer said:
Zero is a value you will get errors if txtCraftLabEst or txtFirewatchLabEst or
txtCraftSuprLabEst is equal to zero and you try to calculate a value based on
those fields when the value of the field is zero.

If txtEstimatedCost is equal to zero you will automatically get zero returned
as the result - unless the above is true for the denominator (=0) is involved.

You could use
Switch(IsNull([costcode]) Or IsNull(costtype), 0
, [costcode]="013210" And [costtype]="05320" AND txtCraftLabEst<>0,
[txtEstimatedCost]/[txtCraftLabEst]
, [costcode]="020110" and [costtype] ="05320" AND txtFirewatchLabEst<>0,
[txtEstimatedCost]/[txtFirewatchLabEst]
, [costcode]="064201" and [costtype]="05320" AND txtCraftSuprLabEst<>0,
[txtEstimatedCost]/[txtCraftSuprLabEst]
, True, Null)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Alberta said:
OK I got this to work by putting () around [costcode] as well as [costtype].
Could I use the IsNull if the txtEstimatedCost is zero as well? Right now if
the txtEstimatedCost is zero, I get an error message #Num!, same if the
txtActualCost is zero.

:

Hi Laurie,

You could add another IIf() function:

IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions
here] )

Just a note that you can use the Switch() function to eliminate the need
to have all those nested IIf() functions.

=Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And
[costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype]
="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and
[costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null)

Look in your online help for details about the Switch() function.

Clifford Bass

Alberta Rose wrote:
Can anyone help me with this? I need to know where to put the coding to tell
the formula that if there is no value in any of the txt's, to return a value
of zero.

=IIf([costcode]="013210" And [costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],
IIf([costcode]="064201" and [costtype]="05320",
[txtEstimatedCost]/[txtCraftSuprLabEst],Null)))

Thanks so much...Laurie
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

Help with "IIf 1
Can you help me make this string shorter? 2
Query Question regarding IIf's 5
if then else statement 6
"If" Statement help needed 6
How to display zero value in blank fields of Cross Tab query 0
Custom Function to return array 2
Getting rid of a Having clause 1

Top