Converting null value in field to a zero

  • Thread starter Thread starter John R. Youngman
  • Start date Start date
J

John R. Youngman

Hi.

I'm having trouble converting a null value to a 0 (zero) in a query. I have
a field name [DepositBy] and another field named [DepositAmount].

If there is no value in [DepositAmount] I'd like it to show a 0 (zero)
instead of being blank (null). I tried using a "then 0" but it doesn't
work.

Here's the query (in the Total row I'm using an "expression") :

JohnDeposit: Sum(IIf([DepositBy]="3",[DepositAmount],"0"))

TIA

John
 
Use Nz() to specify a zero.

Also, omit the quotes if you want a numeric answer. In fact, converting to
currency will ensure the right data type.

Something like this:
JohnDeposit: CCur(IIf([DepositBy]=3, Nz([DepositAmount],0), 0))

The "Sum()" won't work unless this is a Totals query.
 
Allen,

Thanks for the quick response. Based on your suggestion, here's what I
tried:

JohnDeposit: Sum(CCur(IIf([DepositBy]=1,Nz([DepositAmount],0),0)))

with "Expression" in the Total row.

This still returns a blank (null value). This is a Totals query (there's a
date criteria that filters for all records within a given month) so that's
why I'm using the Sum().

It seems to work without the sum, but then I don't get the aggregate value.

I also tried:

JohnDeposit: Sum(IIf([DepositBy]=1,Nz([DepositAmount],0),0))

and put the currency in the properties area. Still seems to be a null
value.

Any thoughts?

Thanks again.

John


Allen Browne said:
Use Nz() to specify a zero.

Also, omit the quotes if you want a numeric answer. In fact, converting to
currency will ensure the right data type.

Something like this:
JohnDeposit: CCur(IIf([DepositBy]=3, Nz([DepositAmount],0), 0))

The "Sum()" won't work unless this is a Totals query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

John R. Youngman said:
Hi.

I'm having trouble converting a null value to a 0 (zero) in a query. I have
a field name [DepositBy] and another field named [DepositAmount].

If there is no value in [DepositAmount] I'd like it to show a 0 (zero)
instead of being blank (null). I tried using a "then 0" but it doesn't
work.

Here's the query (in the Total row I'm using an "expression") :

JohnDeposit: Sum(IIf([DepositBy]="3",[DepositAmount],"0"))
Click to expand...
Click to expand...
 
Guess I don't follow the DepositBy bit.

Would it be possible to GROUP BY DepositBy rather than use an IIf()
expression? You could then select the value you want from the result.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
John R. Youngman said:
Allen,

Thanks for the quick response. Based on your suggestion, here's what I
tried:

JohnDeposit: Sum(CCur(IIf([DepositBy]=1,Nz([DepositAmount],0),0)))

with "Expression" in the Total row.

This still returns a blank (null value). This is a Totals query (there's a
date criteria that filters for all records within a given month) so that's
why I'm using the Sum().

It seems to work without the sum, but then I don't get the aggregate value.

I also tried:

JohnDeposit: Sum(IIf([DepositBy]=1,Nz([DepositAmount],0),0))

and put the currency in the properties area. Still seems to be a null
value.

Any thoughts?

Thanks again.

John


Allen Browne said:
Use Nz() to specify a zero.

Also, omit the quotes if you want a numeric answer. In fact, converting to
currency will ensure the right data type.

Something like this:
JohnDeposit: CCur(IIf([DepositBy]=3, Nz([DepositAmount],0), 0))

The "Sum()" won't work unless this is a Totals query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

John R. Youngman said:
Hi.

I'm having trouble converting a null value to a 0 (zero) in a query.
Click to expand...
I
have
a field name [DepositBy] and another field named [DepositAmount].

If there is no value in [DepositAmount] I'd like it to show a 0 (zero)
instead of being blank (null). I tried using a "then 0" but it doesn't
work.

Here's the query (in the Total row I'm using an "expression") :

JohnDeposit: Sum(IIf([DepositBy]="3",[DepositAmount],"0"))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top