Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 17 votes, 5.00 average. Display Modes
Old 10-26-2011, 03:24 AM   #1
student
Newly Registered User
 
Join Date: Oct 2011
Posts: 45
Thanks: 3
Thanked 6 Times in 6 Posts
student is on a distinguished road
'iferror' equivalent in access

I have a query producing a large number of records, calculating number manufactured and number sold of each product in inventory. This is based on number of boxes/products/sheets. Some items have not been moved in the month so have no record to pull through to this query so the result produces an #Error result as opposed to a blank or a negative.

In Excel, I'd normally use an IFERROR formula to deal with this, but cannot find a reasonable equivalent of this in Access. The format would be something along the lines of '=iferror(x*y,"")' I have 'x*y', no worries

Any help on a generic formula equivalent would be greatly appreciated, preferably in its most simple form - I know there are several noobs like me out there looking for an easy way around this omission.

Access 2007

Many thanks

student is offline   Reply With Quote
Old 10-26-2011, 11:48 PM   #2
mcalex
Newly Registered User
 
Join Date: Jun 2009
Posts: 135
Thanks: 2
Thanked 10 Times in 8 Posts
mcalex is on a distinguished road
Re: 'iferror' equivalent in access

I haven't come across iferror in excel - maybe it's a 2007/2010 thing. In any event, they managed to get by with this omission up til 2003, by using an if() wrapped around an IsErr(). The only difference in Access is you wrap the if() around an IsError(), so:

If(IsError(x*y), <do nothing code>, x*y)

However, imo a better check would be to see if the number of sold items IsNull, as this is the error you are not interested in, but you may want to see the results of any other errors (like somebody putting a decimal in the field)

hth
mcalex
mcalex is offline   Reply With Quote
Old 10-27-2011, 12:20 AM   #3
student
Newly Registered User
 
Join Date: Oct 2011
Posts: 45
Thanks: 3
Thanked 6 Times in 6 Posts
student is on a distinguished road
Unhappy Re: 'iferror' equivalent in access

Thanks for the response mcalex, but this doesn't seem to work either (unless I'm messing that up as well :s )

The attempts I have made so far include these three:

IIf([QUERY_A]![QTY1] Is Null,0,(IIf([QUERY_B]![QTY2] Is Null,0,( [QUERY_B]![QTY2]*[QUERY_A]![QTY1]))))

IIf(IsError([QUERY_B]![QTY2]*[QUERY_A]![QTY1]),0,([QUERY_B]![QTY2]*[QUERY_A]![QTY1]))


Nz([QUERY_A]![QTY1])*Nz([QUERY_B]![QTY2])

I have increased size of brackets for ease of sight (for my own sanity, if nobody else's!) but these give varying degrees of success, with the nested Iif being the best so far.

The issue seems to be that some records called from QUERY_A (or _B) do not exist, while others that are called have a null value for field QTY1 (or 2) I'm still trying to get my head around which one reacts to which situation, although Nz seems to give the worst results (most errors) overall so far. Perhaps my use of Nz is rubbish?


Sorry - not sure about tagging code here - required? if so, how do I?


Last edited by student; 10-27-2011 at 12:21 AM. Reason: addition
student is offline   Reply With Quote
Old 10-27-2011, 01:41 AM   #4
mcalex
Newly Registered User
 
Join Date: Jun 2009
Posts: 135
Thanks: 2
Thanked 10 Times in 8 Posts
mcalex is on a distinguished road
Re: 'iferror' equivalent in access

not IS NULL; IsNull()

You don't want the SQL predicate (I think that's the word), you want the vba function.

For your first query, try:

IIf(IsNull([QUERY_A]![QTY1]), 0, (IIf(IsNull([QUERY_B]![QTY2]), 0, ([QUERY_B]![QTY2]*[QUERY_A]![QTY1]))))

hth
mcalex
mcalex is offline   Reply With Quote
Old 10-27-2011, 02:08 AM   #5
student
Newly Registered User
 
Join Date: Oct 2011
Posts: 45
Thanks: 3
Thanked 6 Times in 6 Posts
student is on a distinguished road
Re: 'iferror' equivalent in access

Tried your suggestion but gives the same result (although better form, ta).

It would appear that if QTY1 provides a value but QTY2 does not, it errors; if QTY2 provides but QTY1 does not, it's fine. Incidentally, if I copy the line from results and paste into Excel, the error fields show as blank, not #error. Is that normal?

Thanks


Incidentally - any idea why the nz solution failed to resolve? It's entirely new to me!

Last edited by student; 10-27-2011 at 02:18 AM. Reason: additional follow-up
student is offline   Reply With Quote
Old 10-27-2011, 02:19 AM   #6
mcalex
Newly Registered User
 
Join Date: Jun 2009
Posts: 135
Thanks: 2
Thanked 10 Times in 8 Posts
mcalex is on a distinguished road
Re: 'iferror' equivalent in access

Quote:
Originally Posted by student View Post
It would appear that if QTY1 provides a value but QTY2 does not, it errors; if QTY2 provides but QTY1 does not, it's fine.
That sounds like a logic problem in the IIf(), or maybe there's a problem with QUERY_A itself (not just the Qty1 value)

Might need to do some investigating (eg using Debug.Print) with what the values actually are so you can work out how to treat them.

cheers

mcalex
mcalex is offline   Reply With Quote
Old 10-27-2011, 02:34 AM   #7
PeterF
Newly Registered User
 
Join Date: Jun 2006
Posts: 289
Thanks: 2
Thanked 45 Times in 45 Posts
PeterF is on a distinguished road
Re: 'iferror' equivalent in access

Quote:
Originally Posted by student View Post
Incidentally - any idea why the nz solution failed to resolve? It's entirely new to me!
As you didn't provide a value to use if null the function wont work.
Nz(FieldToTest, ReplaceValue)
In your case:
Code:
Nz([QUERY_A]![QTY1], 0)*Nz([QUERY_B]![QTY2], 0)

PeterF is offline   Reply With Quote
Old 10-27-2011, 02:42 AM   #8
student
Newly Registered User
 
Join Date: Oct 2011
Posts: 45
Thanks: 3
Thanked 6 Times in 6 Posts
student is on a distinguished road
Re: 'iferror' equivalent in access

Quote:
Originally Posted by PeterF View Post

As you didn't provide a value to use if null the function wont work.
Nz(FieldToTest, ReplaceValue)

[/CODE]
It was my understanding that the 'ReplaceValue' was an optional field, as per MS:

office.microsoft. com /en-us/access-help/nz-function-HA001228890.aspx

(sorry - no 10posts as yet & don't want to post crap to include the reference)
student is offline   Reply With Quote
Old 10-27-2011, 03:20 AM   #9
PeterF
Newly Registered User
 
Join Date: Jun 2006
Posts: 289
Thanks: 2
Thanked 45 Times in 45 Posts
PeterF is on a distinguished road
Re: 'iferror' equivalent in access

Quote:
Originally Posted by student View Post
It was my understanding that the 'ReplaceValue' was an optional field, as per MS:
http://office.microsoft.com/en-us/ac...001228890.aspx
There's a remark "unless used in a query" in the MS article. But no Replacevalue would generate a zero lenght string that wil generate a error when used in math functions.
PeterF is offline   Reply With Quote
Old 10-27-2011, 03:25 AM   #10
MSAccessRookie
AWF VIP
 
MSAccessRookie's Avatar
 
Join Date: May 2008
Location: Central NJ, USA
Posts: 3,428
Thanks: 13
Thanked 127 Times in 122 Posts
MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough
Re: 'iferror' equivalent in access

I may be missing a post here, but I do not see any reason for that code to generate an error. It is, infact, possible that I do not see the error in the code because there is no error in this code at all. What I have not seen yet is the SQL Code that produces the results that you refer to as QueryA and QueryB. If there is an error anywhere in either of those Queries (or any Sub-Query that they refer to), it might look like an error in your Query.


NOTE:

After posting my comment, I noticed the last reply by PeterF. Consider what he said about Nz(). After all, the primary reason for using Nz() is to allow a Query to provide a default value when none is available. Not using the parameter, (and therefore using an undefined value), seems to me to defeat the purpose of using Nz().

If that does not resolve the issue, then post the remaining code as I described, and we will see what we can do for you.
__________________
No one is expected to know everything and we can all learn from each other if we try.

This forum is about taking the next step (or perhaps the next two steps) toward becoming a better Access programmer, and the AWF Forum Members will help you on your journey.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by MSAccessRookie; 10-27-2011 at 03:34 AM. Reason: Add the Comment
MSAccessRookie is offline   Reply With Quote
Old 10-27-2011, 03:30 AM   #11
student
Newly Registered User
 
Join Date: Oct 2011
Posts: 45
Thanks: 3
Thanked 6 Times in 6 Posts
student is on a distinguished road
Re: 'iferror' equivalent in access

Quote:
Originally Posted by PeterF View Post
There's a remark "unless used in a query" in the MS article.
Very good call sir - I quoted the MS site as classic but had based my attempts on other sites which missed that point.

Quote:
Originally Posted by PeterF View Post
But no Replacevalue would generate a zero lenght string that wil generate a error when used in math functions.
Exactly what had been confusing me so much, being relatively new to Access.

Finally figured out why I was receiving the #Error's, and related to your point - one calc field was not returning null values, but a 10-character " " string! Unfortunately the linked-table source was Sage and no-one had ever followed through on this one before. Thanks for all the help. Now leaves me feeling a tad embarrassed, but.....


.....onto the other point to the post - am I right in saying there is no practical equivalent to the Excel 'iferror' function within Access? Clearly in this case it was best to track down the source issue, but would be useful to know regardless.

Cheers again :-)

student is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Equivalent of vlookup in access? kermite Tables 9 08-27-2012 01:30 PM
$_SESSION variable equivalent in Access the_net_2.0 General 5 10-14-2010 08:44 AM
Question Enum equivalent in Access? NotSoRandomOne General 13 09-15-2009 12:50 PM
VBA Equivalent of F9 Refresh (Access 2003) rafi2 Modules & VBA 1 09-04-2007 01:35 PM
What is the split() equivalent in Access '97? dougmcc1 Modules & VBA 1 08-14-2004 06:51 AM




All times are GMT -8. The time now is 04:18 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World