Linked Table Errors

[TKnight]

Hi, I have a DB that links to a txt file export from an Oracle database (that I have no access to) I need the table to be linked because the amount of times i would need to import to keep the data up to date would be too many. There is a date field that I need to use which either contains a date or is null. The problem is that when linked the fields that are empty come through as #Num! Errors. Is there any way I can get round the problem and make the field Null?

I tried
Expr1: IIf(IsError([Authorised Package]),Null,[Authorised Package])

but that still returns #Num! instead of null.

Thanks, Tom.

 

[Mile-O]

Do you have an IsError() function written?

 

[TKnight]

no, i just saw it in the built in functions section of the query builder so I assumed it was built in like Is Null... is that not the case?

Thanks, Tom.

 

[DALIEN51]

Could You...........?

Hi,

Just a thought, could you not write an update query for each field which you run in turn after the import exchanging #num for null?

Regards,

NEIL

 

[TKnight]

Not sure what you mean there. I can't run an update query on a linked table because I can't change the base data can I?

 

[Mile-O]

no, i just saw it in the built in functions section of the query builder so I assumed it was built in like Is Null... is that not the case?

Sorry, my fault. I never knew there was an IsError function in Access. I knew there was one in Excel. Maybe the IsError is native to A2000 and above. I learnt most of the functions via A97 so that's why I thought there was no such thing.

 

[TKnight]

if you drop "IsError" into access help it comes up with a worksheet function so it probably is only in Excel. Doesn't seem to do much in Access anyway I reckon i'm just going to have to import and make it as efficient as possible. The import may take a while but everything will be quicker once that's been completed because I won't constatnly be referencing linked data.

Thanks for your help anyway, Tom.