?
Solved

Error #Type! when field is null

Posted on 2013-10-23
9
Medium Priority
?
4,011 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39595488
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
ID: 39595510
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
ID: 39596272
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
Technology Partners: 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!

 

Author Comment

by:kerikeri
ID: 39598837
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 400 total points
ID: 39599691
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
ID: 39606735
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
ID: 39607525
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
ID: 39615863
;-)
0
 

Expert Comment

by:Lucio Garcia
ID: 40775589
great answer
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

770 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