UNION SELECT Problem

[DS]

I'm having a problem with this UNION query.
The sort order is saying that it can't sort on the [EmpFrstName] & "" ""
& [EmpLastName], it says that it isn't included. Also can I use Union
All as opposed to just UNION?
Thanks
DS

Me.RecordSource = "SELECT tblChecks.ChkBizDay, [EmpFirstName] & "" "" &
[EmpLastName] AS EMP, " & _
"tblChecks.ChkDate, tblChecks.ChkTime, tblChecks.CheckID, " & _
"tblChecks.ChkAlias, tblChecks.ChkCancelled, tblChecks.ChkKillID,
tblChecks.ChkTotal " & _
"FROM tblChecks LEFT JOIN tblEmployees ON tblChecks.ChkServer =
tblEmployees.EmployeeID " & _
"WHERE (((tblChecks.ChkBizDay) >= [Forms]![frmReportDates]![TxtStart] " & _
"And (tblChecks.ChkBizDay) <= [Forms]![frmReportDates]![TxtEnd]) " & _
"And ((tblChecks.ChkCancelled) = -1) And ((tblChecks.ChkKillID) = 0)) " & _
"UNION " & _
"SELECT tblChecks.ChkBizDay, [EmpFirstName] & "" "" & [EmpLastName] AS
EMP, " & _
"tblChecks.ChkDate, tblChecks.ChkTime, tblChecks.CheckID, " & _
"tblChecks.ChkAlias, tblChecks.ChkCancelled, tblChecks.ChkKillID,
tblChecks.ChkTotal " & _
"FROM tblChecks LEFT JOIN tblEmployees ON tblChecks.ChkServer =
tblEmployees.EmployeeID IN '//Backoffice/Warehouse/History.mdb' " & _
"WHERE (((tblChecks.ChkBizDay) >= [Forms]![frmReportDates]![TxtStart] " & _
"And (tblChecks.ChkBizDay) <= [Forms]![frmReportDates]![TxtEnd]) " & _
"And ((tblChecks.ChkCancelled) = -1) And ((tblChecks.ChkKillID) = 0)) " & _
"ORDER BY [EmpFirstName] & "" "" & [EmpLastName], tblChecks.ChkDate,
tblChecks.ChkTime;"

 

[Allen Browne]

Order by the 2 fields rather than a concatenated field, i.e:
"ORDER BY [EmpFirstName], [EmpLastName], tblChecks.ChkDate, ...
(You probably want to include the 2 names as fields in their own right,
rather than ORDER BY EMP.)

Just using UNION removes duplicates. Use UNION ALL unless you explicitly
want Access to compare and remove the duplicates (which is slower.)

 

[Jerry Whittle]

One problem may be that you have that combination aliased as EMP. Therefore
the following might work:

ORDER BY EMP, tblChecks.ChkDate, tblChecks.ChkTime;

If not try the following:

ORDER BY 2, 3, 4;

Then there's always the following which should work:

ORDER BY [EmpFirstName], [EmpLastName], tblChecks.ChkDate, tblChecks.ChkTime;

Yes you can use UNION ALL. It is usually faster than a UNION as it doesn't
have to look for duplicates. On the negative side, it will return duplicates,
so make sure that isn't a problem.