Error #Type! when field is null

Hello you experts.  This formula works perfectly when there is data in the txtInvoiced field.  But error #Type! is displayed if this field is blank.  

=IIf(IsNull([subrptWorkRequestInvoices].[Report]![txtInvoiced]),[txtSumCost],[txtSumCost]-[subrptWorkRequestInvoices].[Report]![txtInvoiced])
kerikeriAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
They kicker here (as you can see) is that if the subreport has no records (No data) then it does not appear, and hence no subtotal is available.

So as long as the main report is looking at the subreport, this will not work (in Print Preview)

There are a couple of ways around this.
Here is one.
Hide the Subreport Footer.
Then calculate the Subreport total on the main form, ...but add a condition for:
    HasData=False
...in the subreport
Then for the difference, subtract this new textbox from the existing text box.

Sample attached

JeffCoachman
Access-EEQ82703562010SubReportHa.accdb
0
 
Dale FyeCommented:
Access evaluates both the True and False portions of an IIF() statement.

Returns the error when which field is blank?

I think you can replace that with:

= [txtSumCost] - NZ([subrptWorkRequestInvoices]![Report]![txtInvoiced], 0)
0
 
kerikeriAuthor Commented:
I've replaced the formula to what you've suggested but now the result is an #Name error on every record, not just the ones where txtInvoiced is null.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Jeffrey CoachmanMIS LiasonCommented:
It looks like you may have a circular reference in your formula.

=IIf(IsNull([subrptWorkRequestInvoices].[Report]![txtInvoiced]),[txtSumCost],[txtSumCost]-[subrptWorkRequestInvoices].[Report]![txtInvoiced])

So to be clear, what control is this expression tied to?

Ex:
If you have a control named txtCountry, and you make the controlsource:  
="The Country is " & txtCountry
...you will get the !Type error

If you open the report in design view, does this control have a little green flag in the upper left corner of the control?
Select the control and hover your cursor over the little error box, and you will see the error explained.

JeffCoachman
0
 
kerikeriAuthor Commented:
Hi Jeff, thanks for picking up on this one.  I'll attach a document I've put together with screendumps of what's happening.  The result displays perfectly and as expected in View Report View.  But when I select View Print Preview, it displays an error and of course the printout displays the error as well.  Not a good look!
ReportTypeError.docx
0
 
kerikeriAuthor Commented:
Sorry for the delay in getting back to you ... I'm in New Zealand and yesterday was a public holiday, so I've just read your comments now.  I kind of follow  ... but am puzzled why ACCESS handles the null situation in Report View, but not in Print View.   Makes no sense to me!  But hey, nothing in this world is perfect and this is just one tiny tiny thing in ACCESS that is frustrating.  The good in it far outweighs the bad!  I'll try your suggested change and let you know how I get on.  Thanks again for your time.
0
 
kerikeriAuthor Commented:
Wow, you are good!  Works perfectly, and good to know that HasData is another useful funciton.  Thanks so much.
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
 
Lucio GarciaCommented:
great answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.