Separate data in one column into four columns

[Ernie Sersen]

I have text in one Access Query column separated by dashes and I want to
separate it into four new Access Query columns. Record one may contain
aaa-bbb-ccc-ddd; record two may contain aaaa-bbb-ccccc-dd; etc. I need the
a's, b's, c's and d's all in their own columns.

 

[Michel Walsh]

Hi,



SELECT InStr(fieldName & "-", "-") As FIrstOne,
InStr( FirstOne+1, Fieldname & "--", "-") As SecondOne,
InStr( SecondOne+1, Fieldname & " ---", "-") As ThirdOne,

Left(fieldName, FirstOne - 1) As FirstPart,
Mid(fieldName, FirstOne+1, SecondOne-FirstOne) As SecondPart,
Mid(fieldName, SecondOne+1, ThridOne-FirstOne) As ThirdPart,
Mid(fieldName, ThridOne+1) As ForuthPart,

FROM myTable


Hoping it may help,
Vanderghast, Access MVP

 

[Ernie Sersen]

Thanks, Michel... I'll give it a try!

Ernie

Hi,



SELECT InStr(fieldName & "-", "-") As FIrstOne,
InStr( FirstOne+1, Fieldname & "--", "-") As SecondOne,
InStr( SecondOne+1, Fieldname & " ---", "-") As ThirdOne,

Left(fieldName, FirstOne - 1) As FirstPart,
Mid(fieldName, FirstOne+1, SecondOne-FirstOne) As SecondPart,
Mid(fieldName, SecondOne+1, ThridOne-FirstOne) As ThirdPart,
Mid(fieldName, ThridOne+1) As ForuthPart,

FROM myTable


Hoping it may help,
Vanderghast, Access MVP

 

[Van T. Dinh]

Check Access VB Help on the Split() function also.