Setting null currency field to 0

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field in my query [sumofonhand] with a data-type as currency
(because the table field is currency). To eliminate null values in my field
I have the following expression in the query: sumonhand:nz([sumofonhand],0).
But, the results replace null values with 0, but now the data-type is number.
How can I make the null values in the fileds $0 without changing the
data-typ?
Thanks
 
Don't really understand the question but is the answer:

formatcurrency(nz([etc.....

?

Cheers
 
When I use Sum: formatcurrency(nz([SumOfOnHandNoRequire],0),0) the null
values are replaced with $0.00, which is what I need, but now the records
don't sort properly. What is happening to the format? Thanks

bin_leigh said:
Don't really understand the question but is the answer:

formatcurrency(nz([etc.....

?

Cheers

Alex said:
I have a field in my query [sumofonhand] with a data-type as currency
(because the table field is currency). To eliminate null values in my field
I have the following expression in the query: sumonhand:nz([sumofonhand],0).
But, the results replace null values with 0, but now the data-type is number.
How can I make the null values in the fileds $0 without changing the
data-typ?
Thanks
Click to expand...
Click to expand...
 
A null is not a value... its empty.

Equally format is just a way of illustrating the data.. it doesn't change
the value.

if you're sorting the records Null comes before 0, then 1 and so on.

Is this your problem?

You might want to change the value instead of formatting it and nz-ing it.

Regards.

Alex said:
When I use Sum: formatcurrency(nz([SumOfOnHandNoRequire],0),0) the null
values are replaced with $0.00, which is what I need, but now the records
don't sort properly. What is happening to the format? Thanks

bin_leigh said:
Don't really understand the question but is the answer:

formatcurrency(nz([etc.....

?

Cheers

Alex said:
I have a field in my query [sumofonhand] with a data-type as currency
(because the table field is currency). To eliminate null values in my field
I have the following expression in the query: sumonhand:nz([sumofonhand],0).
But, the results replace null values with 0, but now the data-type is number.
How can I make the null values in the fileds $0 without changing the
data-typ?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
John said:
Try forcing the data type using CCur

CCur(NZ(SumOfOnHand,0))
Click to expand...

Slightly pedantic point... if the column is CURRENCY then you only need
to cast the zero as currency i.e.

SELECT
CCUR(64000) AS test_value,
IIF(test_value IS NULL, CCUR(0), test_value) AS result,
TYPENAME(result) AS result_type_name

SELECT
NULL AS test_value,
IIF(test_value IS NULL, CCUR(0), test_value) AS result,
TYPENAME(result) AS result_type_name
 
CCur is perfect - thanks John, you are always such a big help.


John Spencer (MVP) said:
Try forcing the data type using CCur

CCur(NZ(SumOfOnHand,0))
I have a field in my query [sumofonhand] with a data-type as currency
(because the table field is currency). To eliminate null values in my field
I have the following expression in the query: sumonhand:nz([sumofonhand],0).
But, the results replace null values with 0, but now the data-type is number.
How can I make the null values in the fileds $0 without changing the
data-typ?
Thanks
Click to expand...
Click to expand...
 
Back
Top