I think you are getting an error because there is not a field named "0" in the ORDER BY in the first SELECT of the UNION query:
Code:
SELECT TempPOACKHeader.PONo, "0" AS LineNo, [TempPOACKHeader]![HeaderData] AS ACKData
FROM TempPOACKHeader
ORDER BY TempPOACKHeader.PONo, "0"
UNION ALL SELECT TempPOACKDetail.PONo, TempPOACKDetail.LineNo, [TempPOACKDetail]![DetailData] AS ACKData
FROM TempPOACKDetail
ORDER BY TempPOACKHeader.PONo, LineNo;
Also, in the Union query, the second SELECT has the wrong domain in the ORDER BY clause
Try this:
Code:
SELECT TempPOACKHeader.PONo, 0 AS LineNo, [TempPOACKHeader]![HeaderData] AS ACKData
FROM TempPOACKHeader
ORDER BY TempPOACKHeader.PONo, LineNo '<<-- changed from "0"
UNION ALL
SELECT TempPOACKDetail.PONo, TempPOACKDetail.LineNo, [TempPOACKDetail]![DetailData] AS ACKData
FROM TempPOACKDetail
ORDER BY TempPOACKDetail.PONo, LineNo; '<<-- changed from TempPOACKHeader
BTW, putting quotes around a number makes the number text. "0" is not the same as 0!
I cannot determine, from what you have posted, if the field "LineNo" is a number type or text type (in the table).
Great job coming up with the queries!