ORDER BY Problem

[DS]

This SQL UNION works...until I place the order by statement in the mix. I
need the order by to sort Num-Alpha fields...Any help Appreciated.
Thaks
DS

With Me.ListAll
.RowSource = "SELECT tblChecksTMP.ChkTabID, tblChecksTMP.ChkAlias,
tblChecksTMP.CheckID, " & _
"tblChecksTMP.ChkTaxCodeID, tblChecksTMP.ChkGuests,
tblChecksTMP.ChkPaid, tblChecksTMP.ChkServer, " & _
"tblChecksTMP.ChkPrinted, tblChecksTMP.ChkDividedCheck,
tblChecksTMP.ChkTypeID, " & _
"tblChecksTMP.ChkCancelled, tblChecksTMP.ChkFXSep, " & _
"Val(tblChecksTMP.ChkAlias) AS TS, tblChecksTMP.ChkAlias " & _
"FROM tblChecksTMP " & _
"GROUP BY tblChecksTMP.ChkTabID, tblChecksTMP.ChkAlias,
tblChecksTMP.CheckID, tblChecksTMP.ChkTaxCodeID, " & _
"tblChecksTMP.ChkGuests, tblChecksTMP.ChkPaid,
tblChecksTMP.ChkServer, tblChecksTMP.ChkPrinted, " & _
"tblChecksTMP.ChkDividedCheck, tblChecksTMP.ChkTypeID,
tblChecksTMP.ChkCancelled, tblChecksTMP.ChkFXSep " & _
"HAVING tblChecksTMP.ChkDividedCheck=0 AND
tblChecksTMP.ChkCancelled=0 " & _
"UNION " & _
"SELECT tblChecks.ChkTabID, tblChecks.ChkAlias, tblChecks.CheckID, "
& _
"tblChecks.ChkTaxCodeID, tblChecks.ChkGuests, tblChecks.ChkPaid,
tblChecks.ChkServer, " & _
"tblChecks.ChkPrinted, tblChecks.ChkDividedCheck,
tblChecks.ChkTypeID, " & _
"tblChecks.ChkCancelled, tblChecks.ChkFXSep, " & _
"Val(tblChecks.ChkAlias) AS TS, tblChecks.ChkAlias " & _
"FROM tblChecks " & _
"GROUP BY tblChecks.ChkTabID, tblChecks.ChkAlias, tblChecks.CheckID,
tblChecks.ChkTaxCodeID, " & _
"tblChecks.ChkGuests, tblChecks.ChkPaid, tblChecks.ChkServer,
tblChecks.ChkPrinted, " & _
"tblChecks.ChkDividedCheck, tblChecks.ChkTypeID,
tblChecks.ChkCancelled, tblChecks.ChkFXSep " & _
"HAVING tblChecks.ChkDividedCheck=0 AND tblChecks.ChkCancelled=0 " &
_
"ORDER BY Val(tblChecks.ChkAlias), tblChecks.ChkAlias;"
.ColumnCount = 14
.ColumnWidths = "0 in;2 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;0
in;0 in;0 in;0 in"
.Requery
End With

 

[Graham Mandeno]

Hi DS

What happens if you copy your RowSource string and paste it into the SQL
window of a new query?

Does it run? Does it give any indication of the nature of the error?

 

[George Nicholson]

"ORDER BY Val(tblChecks.ChkAlias), tblChecks.ChkAlias;"

Try:
"ORDER BY Val(tblChecksTMP.ChkAlias), tblChecksTMP.ChkAlias;"
or
"ORDER BY Val(ChkAlias), ChkAlias;"

The ORDER BY in UNION queries has to refer to fields used in the first
SELECT statement (which is not tblChecks)

 

[DS]

I think the Problem is that I have two tables, both with the same exact Info
but with different table names. The error I get is that the ORDER BY is for
one of those two tables only and excludes the other table.
Thanks
DS

 

[Graham Mandeno]

Hi DS

I think George hit the nail on the head. The field names for a UNION query
are derived from the first query in the union. Therefore, there is no field
named "tblChecks.ChkAlias" to order by.

Have you tried:
ORDER BY Val(ChkAlias), ChkAlias
as he suggested?