Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error #Type! when field is null

Posted on 2013-10-23
9
Medium Priority
?
4,317 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

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!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…

618 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