Import of iregular negative numbers - i.e. negatives shown as "200

[Guest]

I am having a problem importing negative number entries from a text file
(with over 200K lines of data - so manipulation in excel is NOT an option)
that were output from SAP with the negative indicator following the number,
e.g. "2000-" rather than the usual negative number format, e.g. "-2000" When
I attempt an import into Access with the field designated as a number such
records will generate an import error. Is anyone aware of any fixes so these
will show up as negatives in Access?

Thanks to all.

 

[Guest]

access will only import those numbers as text, so import the lot as text.
once in access, you can make another field (number type) and run update
queries, using iif(right(string,1)="-",val(string)*-1,val(string)) to get
them right.
or something like that

 

[James A. Fortune]

I am having a problem importing negative number entries from a text file
(with over 200K lines of data - so manipulation in excel is NOT an option)
that were output from SAP with the negative indicator following the number,
e.g. "2000-" rather than the usual negative number format, e.g. "-2000" When
I attempt an import into Access with the field designated as a number such
records will generate an import error. Is anyone aware of any fixes so these
will show up as negatives in Access?

Thanks to all.

Import the values as text, then use

(2 * Abs(InStr([MyText], "-") = 0) - 1) * Val([MyText])

to get the converted values.

(2 * Abs(InStr("2000-", "-") = 0) - 1) * Val("2000-") ==> -2000
(2 * Abs(InStr("2000", "-") = 0) - 1) * Val("2000") ==> 2000

The 2 * Abs(x) - 1 changes the 0, -1 to -1 and 1.

If "-" is not found, True causes 1 to be the multiplier. If "-" is
found, False causes -1 to be the multiplier.

James A. Fortune