Extract the first paragraph

[Guest]

I have a field called case_detail into which several paragraphs can be typed.
When the case is ready to be summarized, the assistant adds her summary
paragraph to the beginning of this field, separated from the other paragraphs
by a hard return. I need to create a new field which gives me only her
paragraph. One of the programmers gave me some code he thought would work
but I'm getting a syntax error "missing operator" as shown below. I know
very little about SQL, and could very well have stuff out of order, etc.
Please help someone less gifted than yourself!

SELECT dbo_KCCMS_Journal.user_case_no, dbo_KCCMS_Journal.store_number,
dbo_KCCMS_Journal.incident_type, dbo_KCCMS_Journal.comments,

"'Syntax error (missing operator) in query expression 'case
charindex(char(10), case_detail)
when 0 then case_detail
else substring(case_detail,1,charindex(char(10),case_detail))
end as detail_line FROM dbo_KCCMS_Case_Detail,"

case charindex(char(10), case_detail)
when 0 then case_detail
else substring(case_detail,1,charindex(char(10),case_detail))
end as detail_line FROM dbo_KCCMS_Case_Detail,

dbo_KCCMS_Journal.status, dbo_KCCMS_Journal.case_opened
FROM dbo_KCCMS_Journal
WHERE (((dbo_KCCMS_Journal.case_opened) Between [Beg Date] And [End Date]));

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The above query is an T-SQL query. It appears you are running it from
Access (JET). This will not work 'cuz JET doesn't support the CASE
statement. Anyway, the SubString() function is incorrect.

If you want to run this from Access (not as an SQL Pass Through query)
try this (put it in the Query's SQL view):

PARAMETERS [Beg Date] Date, [End Date] Date;

SELECT user_case_no, store_number, incident_type, comments,

IIf(Instr(case_detail,Chr(10))=0, case_detail,
Mid(case_detail, 1, Instr(case_detail, Chr(10))-1)) As detail_line
status, case_opened

FROM dbo_KCCMS_Journal

WHERE case_opened Between [Beg Date] And [End Date]

It would probably be a better idea to factor out the "detail_line" into
another column, since it seems to be important enough to go thru this
parsing rigamarole.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnfbYYechKqOuFEgEQKX7gCbBrm1wAdZyL6A7gCjMjgqJmKrrCQAnjs/
CUKkYL+75dG/l71spdnFgqk2
=hZon
-----END PGP SIGNATURE-----