Splitting Field Values

[tomc]

I have a db with a field that I need to split and put values into two other fields. The format of field1 is either a-b, aa-b, aa-bb, or a-bb. I want to take the value before the "-" and put it into field2 and then take the value after the "-" and put it into field3 and then delete field1. Is there a straightforward way to do this? I want to do it in the query design grid so let me know what should be in the "criteria" line versus the "update to" line.

I'm using Access 2000 and have approx 5500 records to convert.

Thanks!!

Tom

 

[Jings00]

You should first add the new fields to your table, than use an update query with the following to populate the new fields - names based on yours.

Field2=Left([Field1],InStr([Field1],"-")-1)

Field3=Right([Field1],Len([Field1])-InStr([Field1],"-"))

Test the results, then delete field1

 

[tomc]

Thanks!! That did the trick.