We are going to start receiving a file from a vendor for upload into another system. They can't seem to get us the file format we want. After a LOT of back and forth I just want to try and do something on our own. I was like "hey, I can do that in Access easy. I used to do stuff like that all the time!" Seems SUPER EASY until I realized I haven't touched an Access Database in like 8 or 9 years......... and apparently have forgotten about everything I knew.
The basic idea is, import a CSV file (only 3 fields). Code, Amount, Description
SAMPLE
1000000001111000,-194.45,SOME TEXT DESCRIPTION
All I then need to do is manipulate the code field to this format: 10-0-000-000-1111.000
So I'm inserting hyphens at positions 3, 5, 9, 13
And inserting a period at position 18
Seems like a job for an update query?
Then I'll export it out into a new CSV/txt file for import into our system. Empty out the table, exit, etc...
I manually imported some sample data for now (will likely create a macro to automate later) and started messing with an update query but can't seem to get the right results. Should I be using the SET command? Am I on the right track?
UPDATE table
SET code = Left(code,2) & "-" & ???????