Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Coping with errors in linked spreadsheets (2000SP3)

    For historical reasons I have data entered on individuals in both an access database and an excel workbook. For human reasons there is ongoing data entry into both these apps, though not for too much longer. I wanted to link three of the sheets as tables in the database so I can check where we are with record entry in either. When I tried linking whole sheets, the databse became intolerably slow, so I created 3 new sheets to hold links to the relevant data and used them as the basis for linked tables in the database. The orginal sheets contains a number of vlookups, as they are transposing wide data to long, some of which miss, and this eventually feeds through into #NUM!'s in the linked table.

    My problem is I can't get Access to handle these errors - the obvious technique of having a query with an expression IIF(ISERROR([Field0]),Null,[Field0]) doesn't work, even an expression =ISERROR([Field0]) returns #NUM! rather than True.

    Why can't Access handle these errors in a query? Is there any way round this?

    Thanks

    Mark

    One possible option is to alter the links in the second order spreadsheets, although this will leave me with the old excel can't-return-null-from-a-function problem. Although 2 of the fields could be set to 0 if null and manipulated accordingly, 0 already has meaning in the other 2 columns, so that's not appropriate.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Coping with errors in linked spreadsheets (2000SP3)

    The function IsError in Access is a VBA function; it is not the same as the Excel worksheet function ISERROR. The VBA function is meant for use in a module. You will have to handle the error in the Excel spreadsheets using the ISERROR worksheet function, in the form

    =IF(ISERROR(some_expression),0,some_expression)

    or

    =IF(ISERROR(some_expression),"",some_expression)

    where some_expression is the expression that may return an error value.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Coping with errors in linked spreadsheets (200

    So can't be used in an expression in a query then - the query runs OK with ISERROR([Field0]) as part of an expression, which made me think it was legit, but didn't have the desired effect.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Coping with errors in linked spreadsheets (200

    Your idea is correct - IsError can be used in expressions in queries, but it doesn't do what you want, since it is not equivalent to the Excel worksheet function ISERROR.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •