Moving Information

[Misty]

Hi everyone,
I've run into a problem I've never tried before. I have a table that has a text field like: Bldg2-1st_Flr-Column_10B

What I have to end up with is:

Bldg2
1st
10B

Each in their own fields named Bldg, Floor and Column Number.


I have never even tried anything like this. I bet it can be done, but I'm still a beginner in VBA so I don't know how to even start.

Can someone please help me out here? My boss sent me an Excel spreadsheet with the new fields and I have an Access 2K db with the old field. I will import the excel ss into Access and wait to see if someone helps me.

Thanks a bunch,

Misty

 

[dcx693]

If you permanently need to store the new data (sounds like you do), create an update query to split the data and place it into your new fields. Use string functions to split out the data the way you want it. That is, assuming all the data is formatted exactly the same way as in your example. That's a big IF.

1- Find the first "-" character and take all the characters to the left of it to get the building number. Use the Instr() function and the Left() function for that.

2- Find the first "-" character in the string, then find the next occurence of the "_" character. Take the characters in between to be the Floor. Use the Instr() function for that again, along with the Mid() function

3- Find the 2nd "_" character in the string then take the text to the right of that to be the Column. Use the Instr() function again, along with the Mid() or Right() function for that.

Note: in cases where you have to find the 2nd occurence of a character in the string, you will have to make nested calls to the Instr() function, or write your own custom function, as this user did here in order to find the final "\" in a long path: extract file name from path. Here's some more info on the Instr() function: Parsing problem