IIF Expressions

[NDNobbs]

I'm trying to help someone and I'm stuck.

There is a check box that shows Yes and No. If this is checked the item is
funded.

What she wants to do is add the totals of two fields but ONLY if the box is
checked to show it is funded. Here's what she's trying:

=IIf([Funded Y/N]=[Y],[Product Support Labor Balance]+[Balance D00
MFG],IIf([Funded Y/N]=[N],"0"))

 

[KARL DEWEY]

A Yes/No field is stored as -1 for Yes and 0 (zero) for no.
Use the Nz for fields that may be Null to return a zero to be able to do the
math.
Try this --
=IIf([Funded Y/N]=-1,Nz([Product Support Labor Balance],0)+Nz([Balance D00
MFG],0), 0)

 

[NDNobbs]

Karl, we did get it to work, sort of, but even with the box checked it does
not give a correct balance. It just gives "0.00". Does it make a difference
if the field is just a blank without dollar amounts entered?

Any suggestions?

Thanks for the help!
--
NDNobbs

A Yes/No field is stored as -1 for Yes and 0 (zero) for no.
Use the Nz for fields that may be Null to return a zero to be able to do the
math.
Try this --
=IIf([Funded Y/N]=-1,Nz([Product Support Labor Balance],0)+Nz([Balance D00
MFG],0), 0)

--
KARL DEWEY
Build a little - Test a little

I'm trying to help someone and I'm stuck.

There is a check box that shows Yes and No. If this is checked the item is
funded.

What she wants to do is add the totals of two fields but ONLY if the box is
checked to show it is funded. Here's what she's trying:

=IIf([Funded Y/N]=[Y],[Product Support Labor Balance]+[Balance D00
MFG],IIf([Funded Y/N]=[N],"0"))

 

[KARL DEWEY]

Explain a little more.
The Nz function places a zero if a fields does not have a dollar amount.
--
KARL DEWEY
Build a little - Test a little

Karl, we did get it to work, sort of, but even with the box checked it does
not give a correct balance. It just gives "0.00". Does it make a difference
if the field is just a blank without dollar amounts entered?

Any suggestions?

Thanks for the help!
--
NDNobbs

A Yes/No field is stored as -1 for Yes and 0 (zero) for no.
Use the Nz for fields that may be Null to return a zero to be able to do the
math.
Try this --
=IIf([Funded Y/N]=-1,Nz([Product Support Labor Balance],0)+Nz([Balance D00
MFG],0), 0)

--
KARL DEWEY
Build a little - Test a little

I'm trying to help someone and I'm stuck.

There is a check box that shows Yes and No. If this is checked the item is
funded.

What she wants to do is add the totals of two fields but ONLY if the box is
checked to show it is funded. Here's what she's trying:

=IIf([Funded Y/N]=[Y],[Product Support Labor Balance]+[Balance D00
MFG],IIf([Funded Y/N]=[N],"0"))

 

[NDNobbs]

I'm sorry. Maybe this will help.

There is a field entitled 'Funded' that is a yes/no check box.

There are two other fields that have formulas in them to calculate Dollars -
Paid = Balance. One field is D00a Balance. The other is D00b Balance.

We want to combine these balances to get a final total but ONLY want to
calculate those balances if the 'Funded' check box is checked.

Thanks again!
--
NDNobbs

Explain a little more.
The Nz function places a zero if a fields does not have a dollar amount.
--
KARL DEWEY
Build a little - Test a little

Karl, we did get it to work, sort of, but even with the box checked it does
not give a correct balance. It just gives "0.00". Does it make a difference
if the field is just a blank without dollar amounts entered?

Any suggestions?

Thanks for the help!
--
NDNobbs

A Yes/No field is stored as -1 for Yes and 0 (zero) for no.
Use the Nz for fields that may be Null to return a zero to be able to do the
math.
Try this --
=IIf([Funded Y/N]=-1,Nz([Product Support Labor Balance],0)+Nz([Balance D00
MFG],0), 0)

--
KARL DEWEY
Build a little - Test a little


:

I'm trying to help someone and I'm stuck.

There is a check box that shows Yes and No. If this is checked the item is
funded.

What she wants to do is add the totals of two fields but ONLY if the box is
checked to show it is funded. Here's what she's trying:

=IIf([Funded Y/N]=[Y],[Product Support Labor Balance]+[Balance D00
MFG],IIf([Funded Y/N]=[N],"0"))

 

[KARL DEWEY]

It would have been a lot easier if you had posted the real names of the field
in the first place.
Try this --
=IIf([Funded]=-1,Nz([D00a Balance], 0) + Nz([D00b Balance], 0), 0)
--
KARL DEWEY
Build a little - Test a little

I'm sorry. Maybe this will help.

There is a field entitled 'Funded' that is a yes/no check box.

There are two other fields that have formulas in them to calculate Dollars -
Paid = Balance. One field is D00a Balance. The other is D00b Balance.

We want to combine these balances to get a final total but ONLY want to
calculate those balances if the 'Funded' check box is checked.

Thanks again!
--
NDNobbs

Explain a little more.
The Nz function places a zero if a fields does not have a dollar amount.
--
KARL DEWEY
Build a little - Test a little

Karl, we did get it to work, sort of, but even with the box checked it does
not give a correct balance. It just gives "0.00". Does it make a difference
if the field is just a blank without dollar amounts entered?

Any suggestions?

Thanks for the help!
--
NDNobbs


:

A Yes/No field is stored as -1 for Yes and 0 (zero) for no.
Use the Nz for fields that may be Null to return a zero to be able to do the
math.
Try this --
=IIf([Funded Y/N]=-1,Nz([Product Support Labor Balance],0)+Nz([Balance D00
MFG],0), 0)

--
KARL DEWEY
Build a little - Test a little


:

I'm trying to help someone and I'm stuck.

There is a check box that shows Yes and No. If this is checked the item is
funded.

What she wants to do is add the totals of two fields but ONLY if the box is
checked to show it is funded. Here's what she's trying:

=IIf([Funded Y/N]=[Y],[Product Support Labor Balance]+[Balance D00
MFG],IIf([Funded Y/N]=[N],"0"))

 

[Klatuu]

Just a style point, KARL
I recommend using the built in contstants. It makes the code easier to
read. I would suggest
=IIf([Funded]= True,Nz([D00a Balance], 0) + Nz([D00b Balance], 0), 0)

But in reality, I would do it like this:
=IIf([Funded],Nz([D00a Balance], 0) + Nz([D00b Balance], 0), 0)

Since Funded returns either True or False on its own, it need no other
comparison.

It would have been a lot easier if you had posted the real names of the
field
in the first place.
Try this --
=IIf([Funded]=-1,Nz([D00a Balance], 0) + Nz([D00b Balance], 0), 0)
--
KARL DEWEY
Build a little - Test a little

I'm sorry. Maybe this will help.

There is a field entitled 'Funded' that is a yes/no check box.

There are two other fields that have formulas in them to calculate
Dollars -
Paid = Balance. One field is D00a Balance. The other is D00b Balance.

We want to combine these balances to get a final total but ONLY want to
calculate those balances if the 'Funded' check box is checked.

Thanks again!
--
NDNobbs

Explain a little more.
The Nz function places a zero if a fields does not have a dollar
amount.
--
KARL DEWEY
Build a little - Test a little


:

Karl, we did get it to work, sort of, but even with the box checked
it does
not give a correct balance. It just gives "0.00". Does it make a
difference
if the field is just a blank without dollar amounts entered?

Any suggestions?

Thanks for the help!
--
NDNobbs


:

A Yes/No field is stored as -1 for Yes and 0 (zero) for no.
Use the Nz for fields that may be Null to return a zero to be able
to do the
math.
Try this --
=IIf([Funded Y/N]=-1,Nz([Product Support Labor
Balance],0)+Nz([Balance D00
MFG],0), 0)

--
KARL DEWEY
Build a little - Test a little


:

I'm trying to help someone and I'm stuck.

There is a check box that shows Yes and No. If this is checked
the item is
funded.

What she wants to do is add the totals of two fields but ONLY if
the box is
checked to show it is funded. Here's what she's trying:

=IIf([Funded Y/N]=[Y],[Product Support Labor Balance]+[Balance
D00
MFG],IIf([Funded Y/N]=[N],"0"))