type conversion error

[Guest]

Thanks in advance.

Platform: Access 2002

tbl_Emp is a linked table to Excel table named Emp.xls.
Emp.xls has a field named as Emp_ID, with number( eg. 1234) and text type
(eg. manager, supervisor, ...).
Naturally, tbl_Emp's Emp_ID has an error such as "#Num!".

Is it possible to change "Num!" to -1 in a new calculated field New_Emp_ID?

 

[John Vinson]

Thanks in advance.

Platform: Access 2002

tbl_Emp is a linked table to Excel table named Emp.xls.
Emp.xls has a field named as Emp_ID, with number( eg. 1234) and text type
(eg. manager, supervisor, ...).
Naturally, tbl_Emp's Emp_ID has an error such as "#Num!".

You can - hopefully - avoid this by putting an undeniable text string
in Emp_ID on the first row of this spreadsheet. Access must guess the
correct datatype, based on the first few rows of the spreadsheet.
Making the first row text will make the entire column text.

Is it possible to change "Num!" to -1 in a new calculated field New_Emp_ID?

Iif(IsNumeric([Emp_ID]), [Emp_ID], -1)

John W. Vinson[MVP]