Converting Null to Zero for Sum Field not working

[Brett Barry: Go Get Geek!]

Hello,

I'm having this problem and tried the answer in this post:

http://groups.google.com/group/comp.databases.ms-access/browse_thread/thread/fba938d86b839345/a808c90cac4328ff?hl=en&lnk=gst&q=convert+null+values+to+zero#a808c90cac4328ff

However, when I build the expression I get an error message that I
have the wrong number of arguments. Can someone tell me what is wrong
with this code?:

TotalOpenOrders : IIf(IsNull(Sum([OpenOrders:Widg et]!
[SalesOrderLineA mount]), "0", Sum([OpenOrders:Widg et]!
[SalesOrderLineA mount]))

Thanks!

[Salad]

Re: Converting Null to Zero for Sum Field not working

Brett Barry: Go Get Geek! wrote:

Hello,
>
I'm having this problem and tried the answer in this post:

http://groups.google.com/group/comp.databases.ms-access/browse_thread/thread/fba938d86b839345/a808c90cac4328ff?hl=en&lnk=gst&q=convert+null+values+to+zero#a808c90cac4328ff

>
However, when I build the expression I get an error message that I
have the wrong number of arguments. Can someone tell me what is wrong
with this code?:
>
TotalOpenOrders : IIf(IsNull(Sum([OpenOrders:Widg et]!
[SalesOrderLineA mount]), "0", Sum([OpenOrders:Widg et]!
[SalesOrderLineA mount]))
>
Thanks!

Don't know.

Wouldn't
TotalOpenOrders :NZ(Sum(SalesOr derLineAmount), 0)
work just as well...or better?

[Brett Barry: Go Get Geek!]

Re: Converting Null to Zero for Sum Field not working

I forgot to add that I tried just doing the Null function because if I
don't, I get blanks on my report in Access 2007, unless a better way
of doing this is suggested. However, when I build the following
expression, the result is converted to a text field, and is no longer
a number field (which I need on the report).

TotalOpenOrders : Nz(Sum([OpenOrders:Widg et]!
[SalesOrderLineA mount]),"0")

Is there a way to just control the entire report to convert blanks to
"0.00" instead of having to modify every single query to test for
nulls?

Thanks!

[Roger]

Re: Converting Null to Zero for Sum Field not working

On Jun 18, 12:12 am, "Brett Barry: Go Get Geek!"
<brettcba...@gm ail.comwrote:

I forgot to add that I tried just doing the Null function because if I
don't, I get blanks on my report in Access 2007, unless a better way
of doing this is suggested. However, when I build the following
expression, the result is converted to a text field, and is no longer
a number field (which I need on the report).
>
TotalOpenOrders : Nz(Sum([OpenOrders:Widg et]!
[SalesOrderLineA mount]),"0")
>
Is there a way to just control the entire report to convert blanks to
"0.00" instead of having to modify every single query to test for
nulls?
>
Thanks!

1) use nz(x, 0), not nz(x, "0") and you'll have a numeric value

2) on the report field, there's a property called 'format' that allows
you to deal with nulls, and print them as 0, just hit F1 for help

[Brett Barry: Go Get Geek!]

Re: Converting Null to Zero for Sum Field not working

1) use nz(x, 0), not nz(x, "0") and you'll have a numeric value
>
2) on the report field, there's a property called 'format' that allows
you to deal with nulls, and print them as 0, just hit F1 for help

1) Even without the quotes, I get a text field, not numeric. The
"number" is then left aligned in the query result.

2) Can you please give me more detail? I'm using Access 2007 and when
I searched on the keywords, I didn't get anything close to what you
suggested.

Thanks!

[Roger]

Re: Converting Null to Zero for Sum Field not working

On Jun 18, 10:29 am, "Brett Barry: Go Get Geek!"
<brettcba...@gm ail.comwrote:

1) use nz(x, 0), not nz(x, "0") and you'll have a numeric value

>

2) on the report field, there's a property called 'format' that allows
you to deal with nulls, and print them as 0, just hit F1 for help

>
1) Even without the quotes, I get a text field, not numeric. The
"number" is then left aligned in the query result.
>
2) Can you please give me more detail? I'm using Access 2007 and when
I searched on the keywords, I didn't get anything close to what you
suggested.
>
Thanks!

I don't have access2007, but if you open the report, highlight a
field, show properties
you should have a 'format' property
click in it and hit f1 for help

[Brett Barry: Go Get Geek!]

Re: Converting Null to Zero for Sum Field not working

I don't have access2007, but if you open the report, highlight a
field, show properties
you should have a 'format' property
click in it and hit f1 for help

Roger,

Thank you: that helped me out TREMENDOUSLY!

I changed the format to:

0.00;0.00;0.00; 0.00

From Access help:
First The format for positive numbers.
Second The format for negative numbers.
Third The format for zero values.
Fourth The format for Null (Null: A value you can enter in a field or
use in expressions or queries to indicate missing or unknown data.