adding two amounts if one amount is 0 1

[jh3016]

I have a report with a field that sums up two amounts. If one of the amount is equal to 0, I'm getting an #Error. When there is an amount in both of the fields, I get the correct sum.

How do I get Access to know that if amount is blank, then it should add 0 to get the correct sum?

My text box is "=[PTInitial]-[rptPT].Report![Sum Of Time Taken]"

Any help would be appreciated. Thanks.

 

[dhookom]

Is your value really 0 or are there no records in a subreport? Adding a 0 will almost always work. If your issue is with a report or subreport with no data, you can use something like:
=[PTInitial]-IIf([rptPT].Report.HasData, [rptPT].Report![Sum Of Time Taken],0)

 

[jh3016]

Thanks for the reply. I tried using that formula, but I still get an #error. Maybe I didn't state my problem correctly. (I'm just a newbie) My field names are:

Name: PTInitial
Control Source: PTInitial

Name: rptPT
Source Object: Report.rptPT

I want to do PTInitial minus rptPT. But sometimes there is no data in rptPT. At this time, I want Access to treat no data as 0 and still do PTInitial minus 0 equals PTBalance

Thanks again.

 

[jh3016]

I think I got it - probably not the best way, but it works.

1. I made a field in the form with the formula to add a "0" to the field that could possibly contain a null value using the Nz function. Then I made this field invisible.

2. Then to get the sum I added the invisible field and the PTInitial and it worked.

Thanks for your help with this. You pointed me in the right direction!

 

[jh3016]

I spoke too fast. This works for forms, but not for reports. So I'm back to square one. How do I add null values? I can't understand the more complex codes - I'm just not too technical. So here's what I want to do:

My field names are:

Name: PTInitial
Control Source: PTInitial

Name: rptPT
Source Object: Report.rptPT

I want to do PTInitial minus rptPT. But sometimes there is no data in rptPT. At this time, I want Access to treat no data as 0 and still do PTInitial minus 0 equals PTBalance

 

[dhookom]

Try this again. This expression assumes you have a subreport control named rptPT that is a subreport. On that subreport is a text box named [Sum Of Time Taken]. If there is no data in the subreport, then this should work. Make sure the name of the text box containing this expression isn't in a page footer and the name of the text box is not also the name of a field.

=[PTInitial]-IIf([rptPT].Report.HasData, [rptPT].Report![Sum Of Time Taken],0)

 

[jh3016]

OK, now I get it. Thank you. You get a star!