Please Help! "NZ" and "Format" question

[Keetso]

Hi.

I have a select query based on a crosstab query.

All the fields are number (currency) fields. I needed to check for NULL values and used the following to great success:

OakvilleUNUMMS: NZ([qryYTDBusLSMS].[qryYTDBusMS_CrssTb.UNUM],0)

Thus, if the value of "OakvilleUNUMMS" is 13000, then the "NZ" function returns either 13000 OR 0 (If Null).

The problem is that you lose the ability to set the "Format" and "Decimal Places" in "properties".

I used the "Format" command like the following:

FORMAT(NZ([qryYTDBusLSMS].[qryYTDBusMS_CrssTb.UNUM],0),"Currency&quot

This returned the value as $13000.00.

This all works in my report, but when I try to add two of these values together, (for example, $100.00 + $200.00), I expect to get $300.00 as the result. Instead I'm getting,
$100.00$200.00 as the result.

I set the "Format" value in the textbox in the report to "Currency" and the "DecimalPlaces" value to "2" and "Auto" with no result.

Can anyone tell me how I can add these two numbers together correctly or what's wrong?

Many TIA

K

 

[jebry]

Hi!

The first thing I would try is in the query. Instead of

FORMAT(NZ([qryYTDBusLSMS].[qryYTDBusMS_CrssTb.UNUM],0),"Currency&quot

try:

CCur(NZ([qryYTDBusLSMS].[qryYTDBusMS_CrssTb.UNUM],0))

I am not sure that SQL will recognize CCur so, if it doesn't, then I would use

(NZ([qryYTDBusLSMS].[qryYTDBusMS_CrssTb.UNUM],0)

Then you can right click on the field in the query an choose properties from the popup menu. On the format line type in currency. That will leave the field as a numeric value.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[Golom]

Looks like you are attempting to add the values together after you have formatted them. Instead, try adding them together as fields and then formatting the result

   Format ( Nz(Field1,0) + Nz(Field2,0), "Currency")

 

[Keetso]

Hi Golum.

Many thanks. Worked like a charm.

I have one additional question but I think it would be more appropriate to post it in the "Forms" forum.

Again, thanks!

K