Import Text, Automatically Divide by 100 (97)

Topic

New Reply

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.

Reply | Quote

Replies

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).

Reply | Quote

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).

Reply | Quote

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).

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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).

Reply | Quote