null values in reports

[Melinda]

I have a report that shows encumbered funds on a purchase order, modification
and total fund disbused against that purchase orders. If there is "0"
disbursements against that purchase order it gives me an error. I have
tried different code, but nothing seems to work. This is what I have in my
control source

=([encumbered]+nz([sumofmodamount],0)-sum([totalcost]))

I am sure I need something "nz" with the sum, but everthing I try brings
back #Error. Any help would be appreciated. Thanks

 

[Al Campagna]

Melinda,
First, make sure you did not name this calculated field the name of any element in the
calculation.
ex. It should not be name Encumbered, or TotalCost, etc..

Make sure that each of the elements in the calculation are "bound" fields. If, for
example, TotalCost (ex. = Price * Qty) is calculated on the report, it is not "bound",
and can not be summed in the footer.
Add that calculation to your query as a bound field. In query design mode you would
add a column to the grid like this...
TotalCost : Price * Qty
Place the now "bound" field [TotalCost] on the report where you used to have the
unbound calculated field, and then Sum(TotalCost) in the footer will work.
**Check "each" of the calculation elements for this concept...

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Melinda]

the total cost is a calculation of quantity*price per unit in the query for a
total cost. The calculation in the control source is in the purchase order
footer. Are you saying that I should not call it total cost?

Melinda,
First, make sure you did not name this calculated field the name of any element in the
calculation.
ex. It should not be name Encumbered, or TotalCost, etc..

Make sure that each of the elements in the calculation are "bound" fields. If, for
example, TotalCost (ex. = Price * Qty) is calculated on the report, it is not "bound",
and can not be summed in the footer.
Add that calculation to your query as a bound field. In query design mode you would
add a column to the grid like this...
TotalCost : Price * Qty
Place the now "bound" field [TotalCost] on the report where you used to have the
unbound calculated field, and then Sum(TotalCost) in the footer will work.
**Check "each" of the calculation elements for this concept...

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I have a report that shows encumbered funds on a purchase order, modification
and total fund disbused against that purchase orders. If there is "0"
disbursements against that purchase order it gives me an error. I have
tried different code, but nothing seems to work. This is what I have in my
control source

=([encumbered]+nz([sumofmodamount],0)-sum([totalcost]))

I am sure I need something "nz" with the sum, but everthing I try brings
back #Error. Any help would be appreciated. Thanks

 

[Melinda]

I read your message again. That is exactly what I did, but I believe where
the problem lies with when our company has not purchased nothing from
Purchase Order and there is "0" the calculation give me an #error. If I
have expenses against that purchase order everything works fine. I am
thinking that somehow if I have 0 costs I have to put that into the
calcuation.

Melinda,
First, make sure you did not name this calculated field the name of any element in the
calculation.
ex. It should not be name Encumbered, or TotalCost, etc..

Make sure that each of the elements in the calculation are "bound" fields. If, for
example, TotalCost (ex. = Price * Qty) is calculated on the report, it is not "bound",
and can not be summed in the footer.
Add that calculation to your query as a bound field. In query design mode you would
add a column to the grid like this...
TotalCost : Price * Qty
Place the now "bound" field [TotalCost] on the report where you used to have the
unbound calculated field, and then Sum(TotalCost) in the footer will work.
**Check "each" of the calculation elements for this concept...

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I have a report that shows encumbered funds on a purchase order, modification
and total fund disbused against that purchase orders. If there is "0"
disbursements against that purchase order it gives me an error. I have
tried different code, but nothing seems to work. This is what I have in my
control source

=([encumbered]+nz([sumofmodamount],0)-sum([totalcost]))

I am sure I need something "nz" with the sum, but everthing I try brings
back #Error. Any help would be appreciated. Thanks

 

[Al Campagna]

No... not if one of the calculation elements is named [Total Cost]. (your original post
used the name [totalcost] and now you're referring to [total cost]?) I'm assuming that
that is just a typo.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

the total cost is a calculation of quantity*price per unit in the query for a
total cost. The calculation in the control source is in the purchase order
footer. Are you saying that I should not call it total cost?

Melinda,
First, make sure you did not name this calculated field the name of any element in
the
calculation.
ex. It should not be name Encumbered, or TotalCost, etc..

Make sure that each of the elements in the calculation are "bound" fields. If, for
example, TotalCost (ex. = Price * Qty) is calculated on the report, it is not "bound",
and can not be summed in the footer.
Add that calculation to your query as a bound field. In query design mode you would
add a column to the grid like this...
TotalCost : Price * Qty
Place the now "bound" field [TotalCost] on the report where you used to have the
unbound calculated field, and then Sum(TotalCost) in the footer will work.
**Check "each" of the calculation elements for this concept...

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I have a report that shows encumbered funds on a purchase order, modification
and total fund disbused against that purchase orders. If there is "0"
disbursements against that purchase order it gives me an error. I have
tried different code, but nothing seems to work. This is what I have in my
control source

=([encumbered]+nz([sumofmodamount],0)-sum([totalcost]))

I am sure I need something "nz" with the sum, but everthing I try brings
back #Error. Any help would be appreciated. Thanks

 

[Al Campagna]

Melinda,
If there is a possibility that any element in a calculation might yield a Null value,
then that element has to be expressed as an NZ(FieldThatMightBeNullName)
If [Encumbered] might be Null, then NZ([Encumbered]) is needed.., etc for all elements
of the calculation.
If TotalCost may be Null (no items purchased) then use Sum(NZ(TotalCost))
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I read your message again. That is exactly what I did, but I believe where
the problem lies with when our company has not purchased nothing from
Purchase Order and there is "0" the calculation give me an #error. If I
have expenses against that purchase order everything works fine. I am
thinking that somehow if I have 0 costs I have to put that into the
calcuation.

Melinda,
First, make sure you did not name this calculated field the name of any element in
the
calculation.
ex. It should not be name Encumbered, or TotalCost, etc..

Make sure that each of the elements in the calculation are "bound" fields. If, for
example, TotalCost (ex. = Price * Qty) is calculated on the report, it is not "bound",
and can not be summed in the footer.
Add that calculation to your query as a bound field. In query design mode you would
add a column to the grid like this...
TotalCost : Price * Qty
Place the now "bound" field [TotalCost] on the report where you used to have the
unbound calculated field, and then Sum(TotalCost) in the footer will work.
**Check "each" of the calculation elements for this concept...

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I have a report that shows encumbered funds on a purchase order, modification
and total fund disbused against that purchase orders. If there is "0"
disbursements against that purchase order it gives me an error. I have
tried different code, but nothing seems to work. This is what I have in my
control source

=([encumbered]+nz([sumofmodamount],0)-sum([totalcost]))

I am sure I need something "nz" with the sum, but everthing I try brings
back #Error. Any help would be appreciated. Thanks

 

[Sophie]

Melissa,
How about inserting an "IIF" statement in your formula. If the purchase
order is Null make it 0. You should make that to all elements in the
formula in case you have a Null or zero element.

Calculation= IIf(Purchase_order Is Null,0,Purchase_Order) +.....+IIf(...)

It always works for me, I hope for you too.

Jordan
(e-mail address removed)

I read your message again. That is exactly what I did, but I believe
where
the problem lies with when our company has not purchased nothing from
Purchase Order and there is "0" the calculation give me an #error. If I
have expenses against that purchase order everything works fine. I am
thinking that somehow if I have 0 costs I have to put that into the
calcuation.

Melinda,
First, make sure you did not name this calculated field the name of
any element in the
calculation.
ex. It should not be name Encumbered, or TotalCost, etc..

Make sure that each of the elements in the calculation are "bound"
fields. If, for
example, TotalCost (ex. = Price * Qty) is calculated on the report, it
is not "bound",
and can not be summed in the footer.
Add that calculation to your query as a bound field. In query design
mode you would
add a column to the grid like this...
TotalCost : Price * Qty
Place the now "bound" field [TotalCost] on the report where you used
to have the
unbound calculated field, and then Sum(TotalCost) in the footer will
work.
**Check "each" of the calculation elements for this concept...

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I have a report that shows encumbered funds on a purchase order,
modification
and total fund disbused against that purchase orders. If there is "0"
disbursements against that purchase order it gives me an error. I
have
tried different code, but nothing seems to work. This is what I have
in my
control source

=([encumbered]+nz([sumofmodamount],0)-sum([totalcost]))

I am sure I need something "nz" with the sum, but everthing I try
brings
back #Error. Any help would be appreciated. Thanks

 

[Melinda]

I am a beginner at Access so I am not real familiar with "Iif" statements.
This is what I tried but it still does not work.

=([encumbered]+Iif(nz([sumofmodamount])-Iif(nz([total cost])))

I will always have an encumbered amount, but I don't always have a total
cost of items purchased. At times when the report will be ran there will
not be any material charged against a paticular purchase order....this is
where I am having the problem with the purchase order showing up. Thanks
for your help.

Melissa,
How about inserting an "IIF" statement in your formula. If the purchase
order is Null make it 0. You should make that to all elements in the
formula in case you have a Null or zero element.

Calculation= IIf(Purchase_order Is Null,0,Purchase_Order) +.....+IIf(...)

It always works for me, I hope for you too.

Jordan
(e-mail address removed)

I read your message again. That is exactly what I did, but I believe
where
the problem lies with when our company has not purchased nothing from
Purchase Order and there is "0" the calculation give me an #error. If I
have expenses against that purchase order everything works fine. I am
thinking that somehow if I have 0 costs I have to put that into the
calcuation.

Melinda,
First, make sure you did not name this calculated field the name of
any element in the
calculation.
ex. It should not be name Encumbered, or TotalCost, etc..

Make sure that each of the elements in the calculation are "bound"
fields. If, for
example, TotalCost (ex. = Price * Qty) is calculated on the report, it
is not "bound",
and can not be summed in the footer.
Add that calculation to your query as a bound field. In query design
mode you would
add a column to the grid like this...
TotalCost : Price * Qty
Place the now "bound" field [TotalCost] on the report where you used
to have the
unbound calculated field, and then Sum(TotalCost) in the footer will
work.
**Check "each" of the calculation elements for this concept...

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I have a report that shows encumbered funds on a purchase order,
modification
and total fund disbused against that purchase orders. If there is "0"
disbursements against that purchase order it gives me an error. I
have
tried different code, but nothing seems to work. This is what I have
in my
control source

=([encumbered]+nz([sumofmodamount],0)-sum([totalcost]))

I am sure I need something "nz" with the sum, but everthing I try
brings
back #Error. Any help would be appreciated. Thanks