• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

MS access 2003, Calculated field values disappears when ther is no records

On a form, subform2 I list $ amounts of bond deductions and in a field on that forms header area  I sum up total amounts.

This field is a text box with the formula =IIf(Sum([amount])="",0,Sum([amount])) as the control source . This formulas sums the amounts below it.

Everything works fine until there are no records to be summed. When there is no records a 0 is displayed into the summed field and then it disappears leaving the summed field blank.

I want the 0 to remain in the field.

Any ideas on this problem would be greatly appreciated.
Lou
0
Lou Dufresne
Asked:
Lou Dufresne
4 Solutions
 
Arthur_WoodCommented:
try this:

=IIf(IsNull([amount]),0,Sum([amount]))

AW
0
 
mbizupCommented:
Try using NZ:

 =IIf(nz(Sum([amount]))="",0,Sum([amount]))
0
 
puppydogbuddyCommented:
=IIf(Sum(nz([amount],"")="",0,Sum([amount]))
0
 
rockiroadsCommented:
I would use NZ as other experts have demonstrated

But it may be possible for you to use it without the IIF

e.g



Sum(CINt(NZ(amount,0)))
or
Sum(Val(NZ(amount,0)))

0
 
Lou DufresneIT Business Analyst CSM / Project ManagerAuthor Commented:
Thank you all for your quick response. Here is what I found out

Suggestion that did not solve this situation
Arthur Woods
puppydogbuddy
rockiroads

Suggestions tha did work.
mbizup

Thanks again for all you efforts.

Lou
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now