Nz returns incorrect total where one value is null (1 Viewer)

Lady Gragor

Registered User.
Local time
Today, 21:28
Joined
Nov 4, 2008
Messages
37
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

AWF VIP
Local time
Today, 04:28
Joined
Jul 2, 2005
Messages
13,826
Instead of depending on the default to work correctly use the complete syntax:
Nz([YourFieldName],0)
 

Brianwarnock

Retired
Local time
Today, 10:28
Joined
Jun 2, 2003
Messages
12,701
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

AWF VIP
Local time
Today, 04:28
Joined
Jul 2, 2005
Messages
13,826
Thanks Brian. I was not aware of that. I had the right answer but didn't know the why behind the scenes.
 

Brianwarnock

Retired
Local time
Today, 10:28
Joined
Jun 2, 2003
Messages
12,701
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

AWF VIP
Local time
Today, 04:28
Joined
Jul 2, 2005
Messages
13,826
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

Retired
Local time
Today, 10:28
Joined
Jun 2, 2003
Messages
12,701
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

AWF VIP
Local time
Today, 04:28
Joined
Jul 2, 2005
Messages
13,826
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

Retired
Local time
Today, 10:28
Joined
Jun 2, 2003
Messages
12,701
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

Smeghead
Local time
Today, 03:28
Joined
Jan 12, 2001
Messages
32,059
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

Retired
Local time
Today, 10:28
Joined
Jun 2, 2003
Messages
12,701
Thanks for the input Bob, I'll think about if the time comes. :D

Brian
 

Users who are viewing this thread

Top Bottom