How to split a column into new fields..

[jfiocca]

I am very new to Access, and need a little help explaining how to "slice up" data in a particular field, and move it into new fields.. See example below:

[name]
01234 Generic SES
03434 Generic SES
03989 Generic SES
09343 Generic SES

What I neednew fields)
[number] [type] [division]
01234 Generic SES
03434 Generic SES
03989 Generic SES
09343 Generic SES

Is there a way to specify to extract the numerical portion out of the original field? Also, can I extract the data into other fields using wildcards?? Example...(*GENER*).?

Any help would be GREATLY appreciated..


Thanks,
Joe

 

[cmmrfrds]

The Split Function in Access should do it.

Check this thread for an example.
Thread333-709391

 

[jfiocca]

cmmrfrds,

Thank you very much for your reply. I guess I may be asking in the wrong forum. I am not sure exactly where I would type in the scripts that I see in the thread you reference above. I suppose what I am really looking for is a way to do an update table query by using the criteria requirement. Please, if I am asking in the wrong forum, or group let me know a good source for this information. Thanks again!!

Joe

 

[cmmrfrds]

If the data is always in the same position some other function will work in a query.

01234 Generic SES
03434 Generic SES
03989 Generic SES
09343 Generic SES

If the data above is in a field called var1 in table1
Here is a query.
Select left(var1,5) as first5, mid(var1,7,7) as next7, mid(var1,15,3) as next3 from table1

Lets say table2 has 3 columns called first5, next7, next3.

Insert into table2
Select left(var1,5) as first5, mid(var1,7,7) as next7, mid(var1,15,3) as next3 from table1

The above query assumes 3 columns in table2 and creates a select list from table1 with 3 columns