Is there a text to column in Access? 1

[12donkeys]

Can you do a text to column in access simular to excel? I have a data set i.e. 70604-304-101__33049. I need to seperate the 2 number when there is a double underscore. Is this possible.

 

[MajP]

make two calculated fields in a query
Tbl.fieldName is the name of the field with the string value.

LeftPart: Left(Tbl.fieldName, (InStr(Tbl.fieldName, "__") - 1))

RightPart: Right(Tbl.fieldName, (Len(Tbl.fieldName) - 1) - (InStr(Tbl.fieldName, "__")))

In words:
Left part. Find "__" position, return the left characters up to that position minus 1.

Right Part. Find "__" position number, Find the length of the string, subtract the two, take that many characters from the right side.

 

[MajP]

If you need these permanetly in a table, and you have a bunch of records already in the old format. You could build the query as described, and then do an an update to fill in the new fields of "strLeftPart" and "strRightPart

 

[PHV]

A simpler way for the right part:
RightPart: Mid(Tbl.fieldName, (InStr(Tbl.fieldName, "__") + 2))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886