Nz returns incorrect total where one value is null

[Lady Gragor]

Hi,

Would someone be abe to advise me on this please. I have a query which brings together values of a number of other queries. I then have a field which totals and displays as below.

Criteria 1: Total
Criteria 2: Total
Criteria 3: Total
Criteria 4: Total

My problem is that where a null value is contained within one of the queries, I get an incorrect result. Here is the field expression:-

Total Labour Cost: Sum(Nz([Regular Pay])+Nz([Overtime Pay])+Nz([Allowance & Other Pay])+Nz([Annual Leave])+Nz([LSL])+Nz([Super])+Nz([Payroll Tax])+Nz([Workcover])+Nz([Other]))

Any advice please?

 

[RuralGuy]

Instead of depending on the default to work correctly use the complete syntax:
Nz([YourFieldName],0)

 

[Brianwarnock]

The valueifnull is not optional when used in a query.
From help

If you use the Nz function in an expression in a query without using the valueifnull argument, the results will be a zero-length string in the fields that contain null values.

Brian

 

[RuralGuy]

Thanks Brian. I was not aware of that. I had the right answer but didn't know the why behind the scenes.

 

[Brianwarnock]

Glad to be of assistance. I found the problem for me when I started and suspect it is for newbies is that help for functions usable in SQL or the Design Grid only have help available from VBA, why?

Brian

 

[RuralGuy]

I found the problem for me when I started and suspect it is for newbies is that help for functions usable in SQL or the Design Grid only have help available from VBA, why?

Brian

Can you state that question another way?

 

[Brianwarnock]

LOL

If I am designing a query and wish to get help on a function
eg DateAdd, Datediff, NZ, you name it, why do I have to look it up in VBA help not just ACCESS help?
I could also moan about the difficulty of tracking down aggregate functions help but then as Rich's signature used to say "Seek and ye shall find, except in Access help".


Brian

 

[RuralGuy]

I completely understand. You think that's bad, it looks like everything is merged in Office 2007 and now you can't find anything.

 

[Brianwarnock]

All that I have read about 2007 on here makes me believe that a man of my tender years should retire completely rather than face its hassle, especially as I only do it for pleasure now.

Brian

 

[boblarson]

All that I have read about 2007 on here makes me believe that a man of my tender years should retire completely rather than face its hassle, especially as I only do it for pleasure now.

Brian

That hasn't been my experience Brian. I like it a lot. I don't get to use it yet at this place of employment, but I use it as my default at home. I guess it all depends on how much change you can mentally take. I think a lot of the "problems" with 2007 is based on perception. But that's just my opinion. I really don't think it is as hard as some make it out to be. I do believe it becomes hard for many people because they do not like change and if forced to change many will become hardened against it and will find everything to be a challenge.

 

[Brianwarnock]

Thanks for the input Bob, I'll think about if the time comes.

Brian