Help combine fields onto a single record. 1

[Dirtbike]

I'm starting with:

SONumber tn
00100211678 LABOR OUTSIDE OF CONTRACT
00100211678 SERIAL NUMBER REWORK ON 800 SERIES ENGINES AT CAT PLANT. INCLUDES NECESSARY TOOLS AND
00100211678 TIME TO REMOVE EXISTING NUMBERS, CLEAN, PREP AND RESTAMP NUMBERS PER
00100211678 INSTRUCTIONS OF MHI JAPANESE REP. ON SITE. TIME ALSO INVOLVED IN TRANSPORATION OF
00100211678 MHI REP TO AND FROM JOB SITE.
I need to get to:
00100211678 LABOR OUTSIDE OF CONTRACT SERIAL NUMBER REWORK ON 800 SERIES ENGINES AT CAT PLANT. INCLUDES NECESSARY TOOLS AND TIME TO REMOVE EXISTING NUMBERS, CLEAN, PREP AND RESTAMP NUMBERS PER INSTRUCTIONS OF MHI JAPANESE REP. ON SITE. TIME ALSO INVOLVED IN TRANSPORATION OF MHI REP TO AND FROM JOB SITE.

Any help would be greatly appreciated!!

 

[Remou]

Have a look at:
How to concatenate multiple child records into a single value
faq701-4233

 

[ThomasLafferty]

Is the information shortened because of the length of the data type? You could try setting the tn field's data type to memo, then you could include all of this information in one record.

Tom

Live once die twice; live twice die once.

 

[Dirtbike]

It's not a data type issue, I'm hooking to a legecy Cobol system with ODBC.

 

[Dirtbike]

I'm getting the dreaded data type missmatch in criteria. Here is my SQL:
SELECT tn_p1.SONumber, Concatenate("SELECT tn FROM tn_p1 WHERE SONumber = " & [SONumber]) as Notes
FROM tn_p1
WHERE (((tn_p1.SONumber)="00100211678"));

I have verified I'm ADO. The debug points to a problem here:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

 

[PHV]

Perhaps this ?
SELECT SONumber, Concatenate("SELECT tn FROM tn_p1 WHERE SONumber = [tt][!]'[/!]" [/tt]& [SONumber][!][tt] & "'"[/tt][/!]) as Notes
FROM tn_p1
WHERE SONumber='00100211678';


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886