Solved How to display "0" when there are no records to sum?

[JMongi]

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]

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]

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]

Nz() should work, but IIf() is also an option, just in case.

 

[JMongi]

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]

@ 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]

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.

 

[Minty]

Where you have the Hours field name in your query design try replacing it with

MyHours: Nz([Hours],0)

 

[JMongi]

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]

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]

Glad it worked.
If you switch to SQL view in the query you will see it creates an Alias for your field.

 

[JMongi]

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]

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]

@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]

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]

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]

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]

@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]

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.