Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4639
  • Last Modified:

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])
0
kerikeri
Asked:
kerikeri
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
Jeffrey CoachmanMIS 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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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