UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Why Doesn't Iserror Return True?, Access 2016    
 
   
chrismbaylis
post Oct 14 2019, 01:42 PM
Post#1



Posts: 101
Joined: 25-March 10
From: St Albans, UK


Hey folks,

I'm building an app that takes an incoming data file, dumps it into a staging table using transferspreadsheet, and then compares the layout to a documented layout, before passing/rejecting the file.

If the file passes parsing, it is then loaded to a data table using a mapping process (the reason is the source data file keeps getting changed by a third party and cannot commit to a static layout -this mapping process allows the system to react in a controlled fashion, rather than just falling over). I can accept the change and can manage it, but part of the second stage mapping process includes the creation of calculated fields, which is where my trouble lies.

The logic checks to see if the data type of either field is a number, and also checks to see if the calculation contains a NULL string (which, sadly, is a valid value) - therefore if both tests are true it wraps the calculation in an error trap - e.g.: User Mapping of [Field 1]/[Field 2] now becomes Iif(IsError([Field 1]/[Field 2]),0,[Field 1]/[Field 2])

When I look at the resultant dataset, where there are no null values in either of the fields, then it is calculated correctly - however, where there is a null value, the IsError doesn't trigger correctly, and #Error is displayed instead.

I've even attempted to wrap any numerical fields in NZ() functions to default them to 0 if null.

One question I have is would the IsError trigger if the field contains a BLANK instead of a NULL?

If this isn't the issue - does anyone have any ideas?

Thanks in advance, as always!

Chris
Go to the top of the page
 
theDBguy
post Oct 14 2019, 01:55 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,520
Joined: 19-June 07
From: SunnySandyEggo


Hi Chris. Not sure about trapping the error with IsError() but I just simply try to avoid it. For example, I might try something like:
CODE
IIf(Nz([Field2],0)=0,0,Nz([Field1],0)/IIf(Nz([Field2],0)=0,1,[Field2]))
Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
nuclear_nick
post Oct 14 2019, 01:58 PM
Post#3



Posts: 1,798
Joined: 5-February 06
From: Ohio, USA


There is another way... at least one, perhaps more.

What about...
CODE
Nz([Field 1],1)/Nz([Field 2],1))

… or …
CODE
IIf([Field 1],[Field 1],1) / IIF([Field 2], [Field 2], 1)


(For the uninitiated... the second bit of code there reads "If Field 1 exists, use it, else use the value 1 and divide by if Field 2 exists, use it, else use the value 1. Or something close to that. It's getting time to go home here...)

I'm sure there are a few other ways others will help on...

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
chrismbaylis
post Oct 14 2019, 02:17 PM
Post#4



Posts: 101
Joined: 25-March 10
From: St Albans, UK


Thanks Gents - appreciate the fast responses - will play and see which is easier to implement programmatically.
Go to the top of the page
 
ADezii
post Oct 14 2019, 02:42 PM
Post#5



Posts: 2,688
Joined: 4-February 07
From: USA, Florida, Delray Beach


I do believe that you are using the IsError() Function in the wrong context. IsError() returns a Boolean Value indicating whether or not an Expression is an Error Value. An Error Value is created by converting a Real Number to an Error Value using the CVErr() Function. The following Function will check it's Argument to make sure that it is valid and if not convert it to an Error Value. The return Value of the Function is then examined to see if a Square Root or Error Value is returned.
  1. Function Definition:
    CODE
    Public Function fCalcSquareRoot(varNumber As Variant)
    If IsNumeric(varNumber) And varNumber > 0 Then
      fCalcSquareRoot = Sqr(varNumber)
    Else
      fCalcSquareRoot = CVErr(2001)
    End If
    End Function
  2. Call to Function and examination of Return Value:
    CODE
    Dim varTesVal As Variant
    varTestVal = "49843"
    'varTestVal = "49843A"

    If IsError(fCalcSquareRoot(varTestVal)) Then
      Debug.Print "Cannot perform Square Root (Invalid Argument)"
    Else
      Debug.Print "The Square Root of " & varTestVal & " is " & _
                   fCalcSquareRoot(varTestVal)
    End If
  3. Return Values using both "49843" and "49843A":
    CODE
    The Square Root of 49843 is 223.255459059795
    Cannot perform Square Root (Invalid Argument)
Go to the top of the page
 
nuclear_nick
post Oct 15 2019, 05:49 AM
Post#6



Posts: 1,798
Joined: 5-February 06
From: Ohio, USA


Thank you for the excellent explanation, ADezii.

hat_tip.gif

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
ADezii
post Oct 15 2019, 07:14 AM
Post#7



Posts: 2,688
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif ,nuclear nick.
Go to the top of the page
 
chrismbaylis
post Oct 15 2019, 10:09 AM
Post#8



Posts: 101
Joined: 25-March 10
From: St Albans, UK


Thanks for the explanation - that does make a lot of sense.

It turns out that the fields I was querying didn't contain Nulls, but instead were blank strings - changing the query to validate using <>"" did the trick.

Thanks for everybody's help on this.

Regards,

Chris
Go to the top of the page
 
nuclear_nick
post Oct 15 2019, 10:13 AM
Post#9



Posts: 1,798
Joined: 5-February 06
From: Ohio, USA


Really glad to help! And hey, I learned something too!

Good luck with your project!

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2019 - 01:52 AM