Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    12

    Unanswered: importing text file w/ negative signs on right

    I am importing a text file that has the negative sign on the right side of the field. It appears that the only way to get this into Access is making the field Currency. I have a Quantity field that may have negatives that I do not want to be Currency. I put it as Number, but it's dropping off the negative signs. Is there a way to import without using Currency?

    Thanks!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Short answer.. no not really, not if you want to retain a numeric datatype.

    You could import it as a string, then use an update statement to parse the - to the front of the number, then set the field to whatever numeric type you'd like.

  3. #3
    Join Date
    Apr 2004
    Posts
    11
    Have you tried importing it as a generic or a text field then using an update to put it out front and changing it to a numeric?

  4. #4
    Join Date
    Apr 2004
    Posts
    12
    Originally posted by grapedy
    Have you tried importing it as a generic or a text field then using an update to put it out front and changing it to a numeric?
    Thanks for the aid - I imported as text and I have the entire information there -- how would I do a mass update to fix it within Access?

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    UPDATE yourTable
    SET yourField = iif(RIGHT(yourField, 1) = "-", "-" & LEFT(yourField, LEN(yourField) - 1), yourField)


    That's the basic idea anyways.

    After that, you should be able to change the datatype in the tables design view... assuming all the values are numeric of course.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Teddy Teddy Teddy!
    Right$() and Left$() are faster.



    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by izyrider
    Teddy Teddy Teddy!
    Right$() and Left$() are faster.



    izy
    I know, shame on me.


  8. #8
    Join Date
    Apr 2004
    Posts
    12
    Originally posted by Teddy
    I know, shame on me.

    Where does this code go? I am not "code-oriented", rather, I am used to working in the "user-friendly" environment.

    Thanks!

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well in that case, first I would make a backup copy of your database, just be safe.

    Then create a new query and plop that code into the "sql view"

Posting Permissions

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