Splitting Field - SQL?

[jeffc]

Hello again!

Hoping to use SQL to do this in a query.

In a table I have linked to a text document (have to do it that way) there is a field that holds multiple values.

Meaning there can be some fields with nothing, some with a -, soem with a 3 digit number, some with a single digit followed by a space and then a 3 digit number, and lastly a 10 character text with spaces between each.

What i want to do in the query is when it looks at that field, create 2 fields out of it.

When its the 5 character value (the one that is like '2 345') split it, place the 2 in the first new field, and the 345 in the second new field. If the value in the originals field is not in that format, jsut carry over the value to the second new field.

does that make sense?? haha

thanks in advance!

 

[Jon K]

Try these expressions for the two new fields:-

NewField1: IIf(Len([FieldName])=5, Left([FieldName],1), "")

NewField2: IIf(Len([FieldName])=5, Right([FieldName],3), [FieldName])
.

 

[jeffc]

thank you!!!