'iferror' equivalent in access

student · Oct 26, 2011

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

mcalex · Oct 27, 2011

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

student · Oct 27, 2011

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?

mcalex · Oct 27, 2011

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

student · Oct 27, 2011

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!

mcalex · Oct 27, 2011

student said:
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

PeterF · Oct 27, 2011

student said:
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)

student · Oct 27, 2011

PeterF said:
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)

PeterF · Oct 27, 2011

student said:
It was my understanding that the 'ReplaceValue' was an optional field, as per MS:
http://office.microsoft.com/en-us/access-help/nz-function-HA001228890.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.

MSAccessRookie · Oct 27, 2011

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.

student · Oct 27, 2011

PeterF said:
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.

PeterF said:
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

'iferror' equivalent in access

student

Registered User.

mcalex

Registered User.

student

Registered User.

mcalex

Registered User.

student

Registered User.

mcalex

Registered User.

PeterF

Registered User.

student

Registered User.

PeterF

Registered User.

MSAccessRookie

AWF VIP

student

Registered User.

Similar threads

Users who are viewing this thread