Solved

Error #Type! when field is null

Posted on 2013-10-23
9
2,951 Views
Last Modified: 2015-05-13
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
Comment
Question by:kerikeri
9 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 

Author Comment

by:kerikeri
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:kerikeri
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 100 total points
Comment Utility
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
 

Author Comment

by:kerikeri
Comment Utility
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
 

Author Closing Comment

by:kerikeri
Comment Utility
Wow, you are good!  Works perfectly, and good to know that HasData is another useful funciton.  Thanks so much.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
;-)
0
 

Expert Comment

by:Lucio Garcia
Comment Utility
great answer
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now