changing a Null into a 0 VALUE

[BAMF]

I am having trouble making the Zero from a NZ function into a Currency Value. Someone told me to use VAL, but I can't get it to go.

The first column/expression (I dunno what you call it, I am pretty new to Access) has a list of Distributors.
The next column is a number value (shown as a currency) which I calculated in another query. However, there are only a few number values, the rest are null.
In the next column, I typed:
Purchases: NZ([TotalAmount],0)
The result does not return Currency values (for the 0's or previous dollar amounts). I tried to change it under properties, Format, Currency, but that doesn't work there. I am not sure if this is because the 0's are "string variables" or because I can't change the properties of an extension to a currency amount.

I am sure there are several ways to do this, and I am open to any that someone might know. If you need more information, please feel free to ask, that was kinda hard to describe. Thank you for any replies.

 

[Andrew Fergus]

Hi,

To get the null values showing as zero (per your topic heading) use the following formula :

IIf(IsNull(Sum([your value variable name])),0,Sum([your value variable name]))

AND change the "Sum" in the "Total" part of the query to "Expression".

To format the value as currency, use this instead :

Format(IIf(IsNull(Sum([your value variable name])),0,Sum([your value variable name])),"Currency")

AND don't forget to change the "Sum" in the "Total" part of the query to "Expression".

To get the characters "NZ" in front of the value (I'm not sure if this is what you want or not but given I am from NZ I'm going to tell you anyway ) that is formatted as a currency, use this instead :

"NZ" & Format(IIf(IsNull(Sum([your value variable name])),0,Sum([your value variable name])),"Currency")

AND don't forget to change the "Sum" in the "Total" part of the query to "Expression".

The previous suggestions assume you want to show the nil values - if you don't want to show them then enter "Is Not Null And <>0" (without the quotes) into the criteria section under the value.

HTH, Andrew.

 

[BAMF]

Andrew, thank you for helping. Honestly, the formula you posted does the job perfectly. The only snag is that this project requires us to use the NZ function. I am not too good with it, and it isn't popping up when I run help. However, I know its purpose for this project is to return a 0 when there is a null. We were warned that using NZ returns a "string" variable, and that to convert a "string" into a number, I need to use the "VAL" function. However, I have no idea how to use VAL. I tried to do:

Format(Purchases: NZ([TotalAmount],0),"Currency")

But I get an error. If anyone is familiar with NZ, I would really appreciate any suggestions. Thanks guys

 

[Andrew Fergus]

Please accept my apologies .... *I must read the post properly before replying*

I got this to work :

Format(nz([TotalAmount],0),"Currency")

using some sample data so what sort of error are you getting? Is the data actually null? Are the numbers stored as text or numbers?

If you are doing a total query, then you might want to change the "Total" part of the query to "Expression" and change the formula to this :

Format(Sum(nz([TotalAmount],0)),"Currency")

The "val" function converts a string to numbers by using it like this : val(string). Although, when I used the nz function on some numbers stored as text, it still worked fine without having to use the val function.

To access the proper access help, press Alt F11 and then click the question mark on the toolbar - you will find the nz function in there.

Sorry for pointing you in the wrong direction.

HTH, Andrew.

 

[BAMF]

Hey Andrew, that was exactly what I needed. Thanks for the help man!