Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    ISERROR on #NUM! (2003 sp2)

    Am I crazy or does the ISERROR function not work the same in Access as it does in Excel? I have a linked table with 2 columns of dates. if column 1 is blank, the source code that populates the linked tables returns 00/00/0000. Of course when I link it, the date/time format returns a #NUM! error. I tried to write a formula that basically said if there an error on column1, return what's in column2. Thought it would be simple, but the ISERROR function doesn't return a true/false statement. It returns the same error. Am I doing something wrong here?

    Expr2: IIf(IsError([DATE LAST IN]),[DATE CREATE],[DATE LAST IN])
    thanks
    christine

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

    Re: ISERROR on #NUM! (2003 sp2)

    If you want to replace Date Last In with Date Create if Date Last In is null (missing, blank), try

    Expr2: Nz([Date Last In],[Date Create])

    The Nz function returns the first argument, unless that is null, then it returns the second argument.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: ISERROR on #NUM! (2003 sp2)

    Thanks for your response in this matter, but I'm still having a problem. the value is not null, it an error: #Num!. I need to have a formula to convert the error into either a null or a true/false statement that i could nest with the NZ or iif/then formula.
    thanks
    christine

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

    Re: ISERROR on #NUM! (2003 sp2)

    I don't understand where the #Num comes from. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISERROR on #NUM! (2003 sp2)

    By looking at VBA help, it appears the IsError function does return a true/false value.
    I don't understand the VBA help relevant example though; if the UserFunction has no arguments, you can pass values to it using the expression
    UserFunction = CVErr(32767) <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

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

    Re: ISERROR on #NUM! (2003 sp2)

    It doesn't really matter whether the function has arguments or not. The idea is that you can let a function return an error value if something is wrong. You can then catch this error with IsError.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISERROR on #NUM! (2003 sp2)

    Thank you Hans,
    I hadn't realised the statement
    UserFunction = CVErr(32767)
    is part of the UserFunction code, I was under the impression it was an outside call. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  8. #8
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: ISERROR on #NUM! (2003 sp2)

    The excel file attached shows a dump of the table. The problem is that the table is linked to a text file. the text file will be automatically updated from another program. when i set the link specifications, i established "date last in" and "date create" in date format. where 00/00/0000 appeared in the field under "date last in," access converted that field to a #num! error. i have a query written that is supposed to return date last in. if there is not a valid date there, it's supposed to return date create. if in excel, i would do this simply as IF(ISERROR([DATE LAST IN]), [DATE CREATE], [DATE LAST IN]) I can get there because i don't know how to get the ISERROR part to work. Let me know if you need to see the actual database if this doesn't make any sense (probably doesn't). thanks again.
    thanks
    christine

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

    Re: ISERROR on #NUM! (2003 sp2)

    Access simply cannot handle 00/00/0000 in a date field, so you must specify DATE LAST IN as a text field when linking the text file. You can then convert it to a date in a query:
    <code>
    Expr2: IIf(IsDate(DATE LAST IN]),CDate([DATE LAST IN]),[DATE CREATE])
    </code>
    IsDate will be True for text values representing a 'real' date, and False for 00/00/0000. The CDate function converts the text value to a date value.

Posting Permissions

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