Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste append minus figure (Access 97 SR1)

    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)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste append minus figure (Access 97 SR1)

    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.

  3. #3
    New Lounger
    Join Date
    Jul 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste append minus figure (Access 97 SR1)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste append minus figure (Access 97 SR1)

    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.

  5. #5
    New Lounger
    Join Date
    Jul 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste append minus figure (Access 97 SR1)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •