Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    San Juan, Puerto Rico
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    the sign is at the end of the number (access 2000)

    I want to import a csv text file that
    the records looks like this ...
    0001,hammer,12.25+,25
    0027,hammer bag,4.85-,1
    0035,blue lamp,15.95+,3
    0046,lamp bulb,11.85-,1
    ---this is: ItemId,Description,salesAmt,Quantity

    How can I import this file that the field [salesAMT] have the SIGN at the end of the number ?
    How can I tell access that a field have the sign at the end.

    Thank you

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: the sign is at the end of the number (access 2000)

    I presume that the amount will be imported into the table as a text field.
    You could then introduce another field in the table as a currency field and use a query to update that field with the text amount field, like:
    NewAmtField=left(textAmtField,len(textAmtField)-1) * iif(right(textAmtField,1)="+",1,-1)
    unless someone else has a jazzier idea, if they have, I would also like to see it.
    HTH
    Pat

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: the sign is at the end of the number (access 2000)

    Import your third field as a text.
    Run an update query to modify the third field to a numeric with following expression:
    <pre>IIf(Right([field3],1)="-",Val(Left([field3],Len([field3])-1))*-1,Val(Left([field3],Len([field3])-1)))</pre>

    Francois

Posting Permissions

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