Paste append minus figure (Access 97 SR1)

**Tweeky** · 2003-11-12 07:14

I have written and been using for a while a database that uses a paste append query to change information imported as text to currency. This has worked OK up until now when the text file contained a minus value ( refund ) and the paste append has put this into the currency fields in ( ) like this (73.09)

**HansV** · 2003-11-12 07:33

The parentheses are just another way of formatting negative numbers. Access should treat such numbers correctly, independent of the formatting. Instead of changing the data type, try clearing the Format property to see the "real" data.

If you do want to change the data type, change it to Number with field size Single or Double; these can hold decimals (pennies); the default field size for a Number field is Long Integer. But as I wrote, this shouldn't be necessary.

**Tweeky** · 2003-11-13 07:20

Hi Hans I have looked at the formatting and although it is field type is currency the format is blank. I have tried it by changing the format to currency but that does not work either. The results I am looking at are a report which refers to a query and returns a summary column of costs which adds up fine if one of these refunds is not included but when one of these are in the report list it is ignored. It is not treated a a positive number or the result would be out by twice as much.

**HansV** · 2003-11-13 07:39

Open the Regional Settings control panel and check what "negative number format" is set there. Perhaps it's set to "(1.1)". If so, set it to "-1.1" and click Apply. See if that makes any difference; you may have to import the text file again to get it right.

**Tweeky** · 2003-11-13 09:43

Hi Han Thanks for your help on this. It was not in the regional settings. I have discovered what the problem was led by your first post that Access should handle the figure irrespective of how it showed on the screen. I went back to the start. I fond that it was due to a link to the vat code. All positive items had a vat code of (A Type) but the minus figure had one of ([img]/forums/images/smilies/cool.gif[/img] which did not have a value in my table once I added a ([img]/forums/images/smilies/cool.gif[/img] value the query and corresponding report worked fine.
Thanks again for your help
MICK.

Thread: Paste append minus figure (Access 97 SR1)

Thread Tools