Fix all entries

[slide]

I have a table that has some malformed data, part of one field is in another field. I would like to create a function to go through all the rows and extract the extra data and put it into the correct field. Can anyone help me do this? I can't seem to figure it out for the life of me!

 

[georgedwilkinson]

Not with the information you've given us. Please supply more detail.

 

[slide]

I have a table with 2 fields, Name and Description

For some reason the Description got put in the Name field as well so there is nothing in Description and the Name field has both the Name and Description. EX: "NameSomeDescriptionhere",""

 

[georgedwilkinson]

Do you know how to tell where the name ends and the description begins in a consistent pattern? i.e. Is the name always 1 word, 2 words? Is there a space between the 2 fields...that type of stuff?

 

[slide]

Yes I do, Specifically for my table in Name i have, "02P-203A" and I need to take out "02" and "203" and put those in other fields, so it would be ##p-###* where * is any character and # is a number

 

[slide]

I think I can probably figure out how to parse the string (I reserve the right to be wrong and ask for more help lol) The main thing I don't know how to do is access all the records in VBA

 

[georgedwilkinson]

Some commands you should know for parsing in VB:
right()
left()
mid()
instr()
replace()
IsNumeric()

Also, there are several simple examples on using either DAO or ADO "Recordset" to iterate through and read/write data available on this site and others.

Generally:
create a recordset object and connect to the database with it.
read the first record
start your loop (if not EOF)
parse the data
write the parsed data into the 2 fields (use the "Fields" object)
read the next record
iterate
close the recordset
set the recordset to null

 

[gemma-the-husky]

so is the name always the first 8 characters?

 

[petehilljnr]

You could always use an update query if you aren't comfortable using recordsets etc in VBA.

You'd still need to figure out the syntax to seperate your data (using Left, Mid, Len, Instr, Right etc).

Pete.