Carriage Return in MS Access

[ctate]

I have a query in sql as below - where the scan result feild is a memo feild with multiplue lines of data seperated by carriage returns.

The is no common delimiter at the end of the field (apart from the word 'approx', i was wondering if there is a way of seperating out these lines into indivdiual lines of data.

Is this possible without a delimiter, if not would it be possible with a delimiter added to the lines?

An SQL version of the query is below

[CODE=sql]SELECT [GWQMN 1ST EXTRACT].Pt_Code, [GWQMN 1ST EXTRACT].Name, [GWQMN 1ST EXTRACT].Date, [Accepted Sites].Easting, [Accepted Sites].Northing, [GWQMN 1ST EXTRACT].Determinand_Na me, [GWQMN 1ST EXTRACT].Scan_Result, [GWQMN 1ST EXTRACT].Lab_Comment
FROM [Accepted Sites] INNER JOIN [GWQMN 1ST EXTRACT] ON [Accepted Sites].[WIMS_ REF] = [GWQMN 1ST EXTRACT].Pt_Code
WHERE ((([GWQMN 1ST EXTRACT].Scan_Result)<> "no purgable compounds detected") AND (([GWQMN 1ST EXTRACT].Det)="9247" Or ([GWQMN 1ST EXTRACT].Det)="4084"))
ORDER BY [GWQMN 1ST EXTRACT].Pt_Code, [GWQMN 1ST EXTRACT].Date;
[/CODE]
An example of the scan_result field carriage lines

o-Phenylphenol <0.01ug/L approx
Benzophenone <0.01ug/L approx
Isoproturon 0.02ug/L approx
Benzenemethanol , 4-methyl- 0.05ug/L approx
Formamide, N,N-dibutyl- 0.04ug/L approx
Ethanone, 1,1 -(1,4-phenylene)bis- 0.04ug/L approx
Butylated hydroxytoluene 0.01ug/L approx
Ethanol, 2-butoxy-,phosphate(3:1) 0.26ug/L approx
Phosphoric acid, (1-methylethyl)phe nyl diphenyl ester <0.01ug/L approx
4,4 -Dihydroxydiphen ylsulphone 0.1ug/L approx

[Scott Price]

In VBA you can open the query result as a string value, then split it into several strings using the vbCrLf character as a delimiter (that stands for vbCarriageReturnLineFeed)

Regards,
Scott

[Kris1]

Thank you so much. This was a great help to me. I actually needed to add a hard return in an expression within an Access query. Everytime I added a manual line return using [CTRL][ENTER], Access would change it to a space. Here what I did to make it work:

1. I created a VBA function:

Function InsertLineRetur n()
InsertLineRetur n = vbCrLf
End Function

2. In my query, I included InsertLineRetur n() wherever I needed a hard return.

My query is fairly small so there wasn't an impact on processing speed - not sure what effect it would have on a large query but it works for me in this case and I'm so happy. That has been bugging me for years!

Anyone with a better idea?