Full Version: Sum and Nz function
UtterAccess Forums > Microsoft® Access > Access Forms
LilAnnCC1
I'm sure this is really simple, but I can't get it to work. I have a formula of Sum([NumofRecords]) on my form. When the total is zero, I need it to read 0. I've tried using nz, but I must have done it wrong.
Can someone help me?
khaos
Try this
um(nz([NumofRecords],0))
HTH
Ken
LilAnnCC1
I tried that and it doesn't work.
The form is based on a query, and counts the number of records in the query for all employees. Then, the form is actually a subform on another form. When that main form comes up, the subform (with my sum figure) comes up for one employee. If that employee has any particular records, it shows their total. But if they have none, the using your above expression, the field still shows blank. This is the way I have it:
=Sum(nz([NumOfRecords],0))
What am I doing wrong.
Luke_H
Hi,
Try the following notation :
Sum(Nz(sales_price,0)) AS MySalesPrice
Luke.
Larry Larsen
Hi
So you counting the number of records found..?
Try this in the control source of a text box on your subform:
=(Count(*)
This will give you the record count within the recordset returned.
Hope I have understood your correctly
HTH's
thumbup.gif
LilAnnCC1
The count worked! Thank you!
Larry Larsen
Hi
Your welcome..
thumbup.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.