Query Line Breaks?

[luvsmel]

Hello All,

I need help dividing data from 1 field in a table to several fields.

The field contains several access codes that are only separated by an MS Access line break - chr(10). The data can consist of numbers and letters and is not any set length.

Example:
T10810
ST123008001
ST05900103

Right now all are in the same field but I need to break it into 3 different fields on the same table so that the related table containing the access codes can read them individually.

Example:
Access Code 1: T10810
Access Code 2: ST123008001
Access Code 3: ST05900103

I would greatly appreciate any tips on how to do this.

Thanks,
Melanie

 

[arnelgp]

update your fields using Update query:

Update table set
[Access Code 1]=Left([Access Code], Instr([Access Code], Chr(10))-1),
[Access Code 2]=Left(mid([Access Code],instr([Access Code],Chr(10))+1), instr(mid([Access Code],instr([Access Code],Chr(10))+1),Chr(10))-1),
[Access Code 3] = Mid([Access Code], InstrRev([Access Code], Chr(10)-1);

 

[luvsmel]

Hi arnelgp,

This almost worked but it doesn't completely send the right data.

So the field can have anywhere from 0-9 Access codes in it. I have filtered out null fields in the query but with the current code it only transfers data if there is something after it.

I need update/delete queries that will strip the data until there is no more and work on both those that have no codes to the person with 9. If there is only 1 code, there is no chr(10) in the next line. I tried to manipulate the code you sent me but it doesn't work.

Is there a way to do this in 1 or more queries?

Thanks,
luvsmel

 

[gemma-the-husky]

can't you use split() based on chr(10), to produce an array of terms, then process the array.

 

[luvsmel]

I have tried many different combinations of the split functions. Since I haven't use this in a long time, I'm not able to understand the difference between the functions. I came up with a few solutions but the require almost 20 queries.

I have changed from line breaks to commas delimiting the data. I have also added a comma at the end of the data.

It appears in the field "Codes" like so:
1 code:
T23000004,

3 codes:
T23000004,ST107008001,T00187,

5 codes:
T23000004,ST107008001,T00187,ST2102501,ST05900103,

9 codes:
T23000004,ST107008001,T00187,ST2102501,ST05900103,T23000024,ST107008021,T002187,ST2102521,

I need to be able to extract the data to 9 different fields:
Code 1, Code 2, Code 3, Code 4, Code 5, Code 6, Code 7, Code 8, Code 9

I'd like to do this with the least amount of queries or by using VBA. Any suggestions?

Thanks,
luvsmel

 

[arnelgp]

use a function to do that.
paste the code in a module.

Public Function fnSplitField(fld As Variant, pos As Integer, Optional delim As string=",") As Variant
fnSplitField = Null
On Error Resume Next
fnSplitField = Split(fld & "", delim)(pos - 1)
End Function

on your select query:

SELECT fnsplitfield([source field],1) AS Expr1, fnsplitfield([source field],2) AS Expr2, fnsplitfield([source field],3) AS Expr3, fnsplitfield([source field],4) AS Expr4, fnsplitfield([source field],5) AS Expr5, fnsplitfield([source field],6) AS Expr6, fnsplitfield([source field],7) AS Expr7, fnsplitfield([source field],8) AS Expr8, fnsplitfield([source field],9) AS Expr9
FROM source;


or on your Insert query:

INSERT INTO target ( code1, code2, code3, code4, code5, code6, code7, code8, code9 )
SELECT fnsplitfield([source field],1) AS Expr1, fnsplitfield([source field],2) AS Expr2, fnsplitfield([source field],3) AS Expr3, fnsplitfield([source field],4) AS Expr4, fnsplitfield([source field],5) AS Expr5, fnsplitfield([source field],6) AS Expr6, fnsplitfield([source field],7) AS Expr7, fnsplitfield([source field],8) AS Expr8, fnsplitfield([source field],9) AS Expr9
FROM source;

 

[luvsmel]

Thank you so much arnelgp,

I was able to get this to work in one query