Importing different types of numbers

[John W]

This newsgroup has been very helpful...hopefully someone will have a
solution for me again. I have an Access application that imports different
types of files to be combined into a set of reports. One of the files that
I import is a .csv file. There are two number columns that I'm having some
problems with. Normally the numbers are in typical currency format
(omitting any zeros that fall at the end), but there are times with these
numbers will come to us as "0000035.81-". How can I configure this field so
that the numbers will import? I can't switch it to text because I need to
be able to perform calculations. Everything I've tried gives me errors.
Any suggestions?

Thanks!

 

[John Nurick]

Hi John,

One approach is to import it as text (maybe to a temporary table),
then use a query to interpret the text, convert to numbers and put
them in a number field. An expression like this will do the job (where
T is the name of the text field:

(IIf(Right([T],1)="-", -1, 1) * CCur(Left([T], Len([T]) -1)))

 

[John W]

I'll give this a try...Thanks!

Hi John,

One approach is to import it as text (maybe to a temporary table),
then use a query to interpret the text, convert to numbers and put
them in a number field. An expression like this will do the job (where
T is the name of the text field:

(IIf(Right([T],1)="-", -1, 1) * CCur(Left([T], Len([T]) -1)))

This newsgroup has been very helpful...hopefully someone will have a
solution for me again. I have an Access application that imports
different
types of files to be combined into a set of reports. One of the files
that
I import is a .csv file. There are two number columns that I'm having
some
problems with. Normally the numbers are in typical currency format
(omitting any zeros that fall at the end), but there are times with these
numbers will come to us as "0000035.81-". How can I configure this field
so
that the numbers will import? I can't switch it to text because I need to
be able to perform calculations. Everything I've tried gives me errors.
Any suggestions?

Thanks!