One of the field in my data contains both numbers and text, which I usually import to Access via linked tables and setting this field type to number by default. However, the rows that contain texts will be imported as #Num!.
I am tying to convert the rows with #Num! to a default value using the function below. However, my query keeps showing #Num! for the rows with this error. Is there a way for Access to recognize #Num! errors? I have tried other functions like IsError but still unable to achieve the desired result.
IIf(IsNumeric([Field]),[Field],0)
This field type has to be in number because I have other queries that need to treat this field as number, hence I would prefer not to import this field as text.
Thanks in advance!