Appropriate data type for data import

[Travis]

I am importing data into Access which should in most cases be a number
(percent investment return on an asset, or a unit price) but in some
cases says NAv when there is no data available.

If I import it all in numerical format the NAvs will be ignored and
presumably turned into zeros.

But a zero return is not the same as no data.

I'd import it as text, but in some cases I may want to numerically
manipulate the imported data, such as dividing by 100 and formatting as
a percent, or multiplying a unit price by the number of units owned by
clients.

Any suggestions?

Travis

 

[SusanV]

Hi Travis,

You can import it as text then manipulate the data as numbers by using
Val...

 

[Douglas J. Steele]

Import into a temporary table, then do an Append query from that temporary
table to the final table, putting logic in your query to do whatever you
want when the field doesn't contain a value.