Trim and keep

[Box 666]

I have a field that is formatted as follows

Brown, John : Consumer Manger Manchester

I want to copy and keep everything to the right of the " : " to a new field.
Could somebody let me know if this is possible if so how?

With thanks

Bob

 

[Ken Snell [MVP]]

You can use an update query to change the data. Assuming that the name of
the original field is ORIGfield, and the name of the field where you want to
put the information is NEWfield, and both are in TableName:

UPDATE TableName
SET NEWfield=Trim(Mid([ORIGfield], InStr([ORIGfield], " : ")+2)),
ORIGfield=Trim(Left([ORIGfield], InStr([ORIGfield], " : ")));