I have a simple 6-table Microsoft Access database that I periodically download certain financial data from Yahoo Financial into. The data is financial information about publicly traded companies. Some of the fields are downloaded into my database in a form other than numeric data, which results in an inability for me to sort or filter on the field. For instance, a percentage might be stored as "1.23%" complete with the percentage sign, stored as text in the field. Or a dollar amount might be sorted as "$100.00" complete with the dollar sign. I cannot just fix the data one time becuase I periodically receive updated downloads and the data being downloaded is formatted as text with this issue. Therefore, I want to have a program written that I can run against the database each time I perform the download to "clean up" the data in these fields.
I will supply the single database file with a relatively small number of records in the file to make the file smaller and easier for you to work with. I will supply the list of each field in each table that I want converted. Most will be to eliminate dollar signs and percentage signs. I have not looked closely at the quantitiy of fields but there are less than 50 total fields of data in the entire 6-table database and probably around 20 to 25 fields need to be converted.
I expect this project will involve writing a simple program that reads all of the records in each table within the database, and for each field that I list as needing to be converted, trim off the dollar sign from the front and/or the percentage sign off the back, and write the record back to the database.
The workproduct needs to be the actual program that will look for the subject Access database file by name in the same folder in which the program you will write resides and run and operate as I described above. I want the source code so that I can made changes to the fields if necessary, as it is possible that the company I obtain the downloads from might change the field names or database structure in the future. I prefer the program to be written in VBA or another standard language in case I need to have it changed in the future.
It should be an hour or so of work for someone that is an expert with Microsoft Access and VBA, or a little longer if you must figure it out.
Email is the best way to reach me. I'm fine with working with anyone anywhere in the world but I can only communicate in English.