Importing *.txt file containing negative numbers

[Alojz]

Hello,
I import *.txt file where there are negative values in source data. The
problem is, that negative value in source list is written as following:
12.457.965,60-
e.g. with minus sign behind the number.
What shall I do to have this record recognized as negative number in Access
? So far, I get error message that this particular item (all negatives) are
not imported. Is there special format for negative numbers I can use to
easily import negatives numbers with minus sign behind?

 

[Douglas J. Steele]

You could try importing the values as text fields, not numbers.

Once you've done that, add a new numeric field to the table, and set its
value using something like:

UPDATE MyTable
SET MyNumericField = IIf(Right([MyTextField], 1) =
"-", -CLng(Left([MyTextField], Len([MyTextField]) - 1), CLng([MyTextField])

 

[Alojz]

Hi,
thx for posting, I did something similar in the meantime, had problems with
thousand separators when text not value, so needed to replace "." with "".
Will check ur code asap and will let u know how it works.

You could try importing the values as text fields, not numbers.

Once you've done that, add a new numeric field to the table, and set its
value using something like:

UPDATE MyTable
SET MyNumericField = IIf(Right([MyTextField], 1) =
"-", -CLng(Left([MyTextField], Len([MyTextField]) - 1), CLng([MyTextField])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello,
I import *.txt file where there are negative values in source data. The
problem is, that negative value in source list is written as following:
12.457.965,60-
e.g. with minus sign behind the number.
What shall I do to have this record recognized as negative number in
Access
? So far, I get error message that this particular item (all negatives)
are
not imported. Is there special format for negative numbers I can use to
easily import negatives numbers with minus sign behind?

 

[Alojz]

sorted out, thx for help

You could try importing the values as text fields, not numbers.

Once you've done that, add a new numeric field to the table, and set its
value using something like:

UPDATE MyTable
SET MyNumericField = IIf(Right([MyTextField], 1) =
"-", -CLng(Left([MyTextField], Len([MyTextField]) - 1), CLng([MyTextField])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello,
I import *.txt file where there are negative values in source data. The
problem is, that negative value in source list is written as following:
12.457.965,60-
e.g. with minus sign behind the number.
What shall I do to have this record recognized as negative number in
Access
? So far, I get error message that this particular item (all negatives)
are
not imported. Is there special format for negative numbers I can use to
easily import negatives numbers with minus sign behind?

 

[RyNC]

Hi Douglas,

This code is almost working for me. I got it to move the negative sign to
the front, but now it is removing all numbers after my decimal point. I've
checked to make sure my formatting for [MyNumericField] is double and 2
decimal points.

For example:

[MyTextField] is 23.32-

I run update query and....

[MyNumericField] is -23


Also, I had to modify your code because there were paranthesis missing.
Here is what I have:

IIf(Right([MyNumericField],1)="-",-CLng(Left([MyTextField],Len([MyTextField])-1)),CLng([MyTextField]))

You could try importing the values as text fields, not numbers.

Once you've done that, add a new numeric field to the table, and set its
value using something like:

UPDATE MyTable
SET MyNumericField = IIf(Right([MyTextField], 1) =
"-", -CLng(Left([MyTextField], Len([MyTextField]) - 1), CLng([MyTextField])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello,
I import *.txt file where there are negative values in source data. The
problem is, that negative value in source list is written as following:
12.457.965,60-
e.g. with minus sign behind the number.
What shall I do to have this record recognized as negative number in
Access
? So far, I get error message that this particular item (all negatives)
are
not imported. Is there special format for negative numbers I can use to
easily import negatives numbers with minus sign behind?