Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Text, Automatically Divide by 100 (97)

    I would like to import a certain text file into Access every month. I've created a macro and an import spec that seems to work fine, except for one thing. I have a number of fields that I've declared as currency fields. My text file does not separate the dollars from the cents, hence the numbers in my table are all 100 times too large. Is there a way to automatically divide all of the currency fields by 100 when I import the file? Either a VBA solution or something else would be great. Thanks.

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

    Re: Import Text, Automatically Divide by 100 (97)

    Create an update query based on the imported table that updates each of the currency fields to [NameOfField]/100. Run the update query immediately after importing the table. If desired, you can create a VBA routine, for example the On Click event procedure for a command button on a form, that imports the text file (DoCmd.TransferText) and runs the query (DoCmd.OpenQuery).

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Import Text, Automatically Divide by 100 (97)

    I'd create a link to the text file so it becomes just another table in the database window. Then create an append query to add the info to my regular table, except that for the currency fields in question you div each by 100 in the query. I think this is safer than importing the data into your main table and then running an update query against it (too much of a chance you may accidentally run it twice).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text, Automatically Divide by 100 (97)

    Hans and Mark,
    Thanks for the replies. I think both are workable ideas and I shouldn't have too much trouble implementing either one.

Posting Permissions

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