ISERROR on #NUM! (2003 sp2)

Topic

New Reply

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])

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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:

Expr2: IIf(IsDate(DATE LAST IN]),CDate([DATE LAST IN]),[DATE CREATE])

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.

Reply | Quote