Separate data in one column into four columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Thanks, Michel... I'll give it a try!

Ernie

Michel Walsh said:
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
Click to expand...
 
Back
Top