I am upgrading an old Access application to a new VB.Net Windows application that still uses an Access database to store data. I have a large DTS procedure that imports/transforms the data for me. I'm having a problem because the old app had the field City/State/Zip and the new app has 3 seperate fields. How can I modify my DTS procedure to split these fields up? In the old app, there is a delimeter of / to work with but I don't know how I can write this into the procedure. Can anyone help here?
I have encountered this problem numerous times when converting or modifying an existing database. I used to resolve the issue using a normal query and then using the built in functions of InStr, Left, Mid and Right if necessary to separate the data into separate fields. (These are calculated fields in the query.) The original data was entered as LastName,FirstName MI (middle initial was optional)
Once I was certain the query returned the correct results, I changed the query to an update query and updated the new table fields.
The examples I have below show what I did when a database did not have separate fields for Last Name and First Name and the delimiter was a comma. I didn't need to use the Right function in this case as I did not need the middle initial but you should be able to see my logic here.
Calculated fields in regular query:
Locate Comma: InStr([app_name],",") 'locates how many characters from the left is the comma.
Last name: Left([app_name],[locate comma]-1) 'populates the last name field and removes the comma
Locate Space: InStr([locate comma]+1,[app_name]," ") 'locates how many characters from the left between comma and next word
WordLength: ([locate space]-[locate comma]) 'determines how many characters to actually return
First Name: Mid([app_name],[locate comma]+1,[wordLength]-1) 'populates first name field and removes trailing space
Now for the bad news. These calculated querys do not work in Access 2002 or 2003. The functions of InStr, Left, Right and Mid only work in VBA.
This necessitates either opening the database in a format that supports it and fixing the problem there before converting the database or writing a VBA procedure to loop through all of the records and separate the data. (When working with an existing prior version, my preference is to use the version the database was created in to fix problems like this and then start from scratch on a new database and import only those tables, queries, forms etc... that I need.
Thanks for your reply. It is interesting to know about the issue of Access 2002+. The database that I am working from was created with Access 2002. I ended up creating a simple VB.Net application to transform the data. Your code was very helpful when I needed to transform the location fields. Thank you.