Solved How to display "0" when there are no records to sum? (1 Viewer)

JMongi

Active member
Local time
Today, 15:11
Joined
Jan 6, 2021
Messages
802
I have a query that shows all projects and the hours attached. The query sums on the field from the appropriate table that tracks these.

My question is, when there are no records to sum, my form shows a blank instead of "0" for the number of hours. Is there a way to handle this within the query itself? VBA? Something else?
 

Minty

AWF VIP
Local time
Today, 19:11
Joined
Jul 26, 2013
Messages
10,355
The Nz() function is probably the simplest way.

NZ(YourField,0)

Normally a Sum would always return 0 unless everything is Null ?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:11
Joined
Feb 28, 2001
Messages
26,999
Wrap the summation field in the NZ function, as for example NZ( SUM( X), 0 )
If that doesn't fix it then we need to see the query.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:11
Joined
Oct 29, 2018
Messages
21,358
Nz() should work, but IIf() is also an option, just in case.
 

JMongi

Active member
Local time
Today, 15:11
Joined
Jan 6, 2021
Messages
802
I understand the concepts you are expressing and as were expressed in other threads. I don't know where to implement what you are talking about?
 

JMongi

Active member
Local time
Today, 15:11
Joined
Jan 6, 2021
Messages
802
@ Minty - Yes, all values are null because there are no corresponding records in the tracker table to sum any values on.

Are we talking about editing the resultant SQL statement after the query is constructed within Access?

Edit: Let me clarify something if it's important. I am summing by selecting "Sum" from the dropdown menu in the query builder under the appropriate Field.
 

JMongi

Active member
Local time
Today, 15:11
Joined
Jan 6, 2021
Messages
802
Query.PNG


My apologies in advance if I'm not understanding something simple or basic. I understand the idea to check if the result is not null and if it is changing the returned value to 0. I'm just not sure where within Access to apply this. I've tried putting something in "Criteria" but get a type mismatch error.
 
Last edited:

Minty

AWF VIP
Local time
Today, 19:11
Joined
Jul 26, 2013
Messages
10,355
Where you have the Hours field name in your query design try replacing it with

MyHours: Nz([Hours],0)
 

JMongi

Active member
Local time
Today, 15:11
Joined
Jan 6, 2021
Messages
802
I would never have thought to mess around with the field name itself. I presume this is checking each return for null and substituting 0 when null. I would still like to understand the proper syntax using a criteria post sum if that is feasible.

Edit: That works, by the way....:)

Edit #2: Rereading the thread, were you all suggesting that your expression go in the Field name? If so, I apologize for assuming it was supposed to go in the criteria section.
 

JMongi

Active member
Local time
Today, 15:11
Joined
Jan 6, 2021
Messages
802
So a syntax explanation for n00bs like me....
If you enter the expression: Nz([Hours],0) in the field portion of the query it will work just fine. However, you will see Access auto change that to Expr1: Nz([Hours],0)
Since you can't do a circular alias Hours: Nz([Hours],0), Minty took the liberty of changing it ahead of time to MyHours: Nz([Hours],0)
I tweaked it to SumHours: Nz([Hours],0) in my query and then updated the control source of the appropriate text box on my form to "SumHours"

I now feel like I have the fish and know how to fish ;)
 

Minty

AWF VIP
Local time
Today, 19:11
Joined
Jul 26, 2013
Messages
10,355
Glad it worked.
If you switch to SQL view in the query you will see it creates an Alias for your field.
 

JMongi

Active member
Local time
Today, 15:11
Joined
Jan 6, 2021
Messages
802
And that's where I could have manually entered the Nz around the sum like others had suggested. I may do that just to see what the resultant query design dispaly looks like.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:11
Joined
Sep 12, 2006
Messages
15,614
The only occasional issue is because you now use a calculated field, you can't edit the hours in that field. You could show the hours AND the calculation, then you could edit the hours, and the calc would either update automatically or a simple bit of code would do it. (either refresh or requery)
 

JMongi

Active member
Local time
Today, 15:11
Joined
Jan 6, 2021
Messages
802
@gemma - I'm not sure what you are referring to, Minty's solution or my musing about putting the Nz around the sum itself? When you say can't edit the hours 'in that field', I presume you are talking about the query field?

In this particular scenario the hours are added and tracked in a separate table. I'm just pulling the sum on a project basis for this form which is view only.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:11
Joined
Feb 28, 2001
Messages
26,999
When a query includes an aggregate, that makes the query uneditable (usually) because when Access goes to write back something, it has an aggregated set of records and doesn't know which record needs to be written back. And it won't automatically do all of them at once. It only wants to update one record. With an aggregate on the line, it can't identify one record because they are ALL possessed of the same values due to the GROUP BY operation that you showed.
 

JMongi

Active member
Local time
Today, 15:11
Joined
Jan 6, 2021
Messages
802
Yes, that's logical and make's sense. It was just unclear if I was being warned about something else. Wanted to be sure.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:11
Joined
Feb 28, 2001
Messages
26,999
The other part of the question is that you wrap the NZ around the SUM aggregate function because you CAN'T wrap it around the individual values. There aren't any individual values. No records were found!.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:11
Joined
Sep 12, 2006
Messages
15,614
@gemma - I'm not sure what you are referring to, Minty's solution or my musing about putting the Nz around the sum itself? When you say can't edit the hours 'in that field', I presume you are talking about the query field?

In this particular scenario the hours are added and tracked in a separate table. I'm just pulling the sum on a project basis for this form which is view only.

I meant if you coerce a query field to a value with nz(), and then display the field in a control on a form, (or directly in the query) you wouldn't be able to edit the value as you would a normal control. It may not matter in this instance.
 

JMongi

Active member
Local time
Today, 15:11
Joined
Jan 6, 2021
Messages
802
Thanks for the heads up. While it is logical, I can see in other forum posts that some still expect that to be possible. So, I appreciate you making sure I understand the limitations.
 

Users who are viewing this thread

Top Bottom