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....08c90cac4328ff

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:Widget]!
[SalesOrderLineAmount]), "0", Sum([OpenOrders:Widget]!
[SalesOrderLineAmount]))

Thanks!

[Salad]

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....08c90cac4328ff

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:Widget]!
[SalesOrderLineAmount]), "0", Sum([OpenOrders:Widget]!
[SalesOrderLineAmount]))

Thanks!

Don't know.

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

[Brett Barry: Go Get Geek!]

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:Widget]!
[SalesOrderLineAmount]),"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]

On Jun 18, 12:12*am, "Brett Barry: Go Get Geek!"
<[e-mail]:

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:Widget]!
[SalesOrderLineAmount]),"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!]

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]

On Jun 18, 10:29*am, "Brett Barry: Go Get Geek!"
<[e-mail]:

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!]

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.