Results 1 to 4 of 4
  1. #1
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74

    IsError returning #error


    Hi all, I am trying to do a very straightforward (or so I thought) query that changes any value with an error to 0. But the IsError() function is itself returning "#error" instead of returning true. Any insight on this?

    query sql:

    Code:
    SELECT DataCalcQ.LineItemID, DataCalcQ.DV, IIf(IsError([DV]),0,[DV]) AS NewDV
    FROM DataCalcQ;
    returns:
    LineItemID DV NewDV
    6161 0 0
    6162 2513 2513
    6163 2557 2557
    6164 1092 1092
    6939 2903 2903
    6940 2903 2903
    6941 2600 2600
    6974 #ERROR #ERROR
    10029 #ERROR #ERROR

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This is one of those cases where what's not working is not helping. Instead, state what's desired based on what you have because the reason for using IsError is not evident. That's for checking if an expression results in an error value.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    For example, the user types in a mathematical expression such as "123 + 456" into a text box called Equation. In the first query, DataCalcQ, DV = eval(equation). But if someone types in "123f+456" it results in error and I need to be able to zero it out if it's an error so it doesn't throw off the total sum of [DV]. I am doing a lot on the front end to prevent garbage entries, but users continue to find new and exciting ways to mess with the data, so I need to be prepared!

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    AFAIK, you cannot use IsError in a query as nothing will resolve to one of the intrinsic errors (e.g. #Error, #Name etc) so that you can catch it. You can get one of these errors as a result, however.

    Methinks you need to perform validation on the form and not try to work every possible fix into a query. F'rinstance, you'll never catch "data type mismatch" in this way. I think by allowing entire expressions/formulae to be entered in a single form control definitely will make things harder - unless only trapping the occurrence of an error is enough. Either that or you'll have to think of every possible error that can be raised (not necessarily every action that can cause one) and trap for them in code.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. IsError function not working
    By Paul H in forum Queries
    Replies: 4
    Last Post: 10-02-2015, 02:30 PM
  2. Replies: 5
    Last Post: 07-15-2015, 01:30 PM
  3. VBA Code Returning Error Run Time Error 3061
    By tgwacker in forum Access
    Replies: 2
    Last Post: 11-24-2013, 11:00 AM
  4. Replies: 1
    Last Post: 11-17-2011, 12:06 PM
  5. IIf(IsError ... problem ...
    By jacek.w.bak in forum Forms
    Replies: 1
    Last Post: 07-07-2011, 11:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums