Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Negative Numbers in Access

    I am trying to import a text file into Access. The issue is that i have negative numbers in the following format: (1,200,567.32). I have tried different import types, double, integer, etc. The only one that brings in the negative number is text. How do i convert this to a number?

    Thanks for your help

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Access is not going to recognise this as a number, so you need to initially import into a text field, and have a second Double field to hold the number. Integers and Long Integers don't have decimal places.
    Then run an Update Query to put a value into the Numerical field. The expression to use is this:

    IIf(Left([Textvalue],1)="(",Val(Mid([textvalue],2,Len([textvalue]-2))),Val([textValue]))
    Val converts a piece of text to a number if the piece of text looks like a number.
    The first test checks if the TextValue starts with a "(", if so it discards the first and last character, and converts the rest.
    If the first character is not "(" it converts the number as it is.

    ConverttoNumber.gif

    In these expressions, you need to use your own field and table names.
    Regards
    John



Posting Permissions

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