D
DS
This SQL statement keeps saying that the Order By part can only use fields
from the first query. I see the fields but perhaps something is missing?
Thanks
DS
Me.RecordSource = "SELECT tblCheckDetailsTMP.CDCheckID,
tblCheckDetailsTMP.CDQuantity, IIf([CDSub]=1,tblItems.ItemName,IIf([CDSub]=2
Or [CDSub]=3,"" "" & tblItems.ItemName,IIf([CDSub]=4,"" "" &
tblItems.ItemName,IIf([CDSub]=5,"" "" &
tblItems.ItemName,IIf([CDSub]=6,tblItems.ItemName))))) AS NAME, " & _
"IPRICE([CDQuantity],[CDPrice],[CDDiscountAmount],[CDDiscountPercent],[CDDiscountDP],[CDDiscountWhere],[CDTaxRate],[CDInclusive])
AS PRICE, tblChecksTMP.ChkDate, tblChecksTMP.ChkTime, [ChkAlias] & "" / "" &
[ChkGuests] AS INFO, tblChecksTMP.ChkServer, tblChecksTMP.ChkDividedCheck, "
& _
"tblCheckDetailsTMP.CDGroupID, tblCheckDetailsTMP.CDSubGroupID,
tblCheckDetailsTMP.CDLineID, tblItems.ItemTypeID, tblChecksTMP.ChkMessage "
& _
"FROM (tblChecksTMP INNER JOIN tblCheckDetailsTMP ON
tblChecksTMP.CheckID = tblCheckDetailsTMP.CDCheckID) INNER JOIN tblItems ON
tblCheckDetailsTMP.CDItemID = tblItems.ItemID " & _
"WHERE (((tblCheckDetailsTMP.CDCheckID) =
[Forms]![frmCheckPreview]![TxtCheckID])) " & _
"UNION ALL " & _
"SELECT tblDiscountDetails.CDCheckID, tblDiscountDetails.CDQuantity, " &
_
"tblDiscounts.DiscountName AS NAME,
DPRICE([CDDiscountDP],[CDDiscountAmount],[CDDiscountPercent]) AS PRICE, " &
_
"tblChecksTMP.ChkDate, tblChecksTMP.ChkTime, """" AS
INFO,tblChecksTMP.ChkServer,tblChecksTMP.ChkDividedCheck, " & _
"tblCheckDetailsTMP.CDGroupID , tblCheckDetailsTMP.CDSubGroupID,
tblCheckDetailsTMP.CDLineID, tblDiscounts.DiscountName,
tblChecksTMP.ChkMessage " & _
"FROM (tblChecksTMP INNER JOIN tblDiscountDetails ON
tblChecksTMP.CheckID = tblDiscountDetails.CDCheckID) " & _
"INNER JOIN tblDiscounts ON tblDiscountDetails.CDDiscountID =
tblDiscounts.DiscountID " & _
"WHERE tblDiscountDetails.CDCheckID = Forms!frmCheckPreview!TxtCheckID "
& _
"ORDER BY tblCheckDetailsTMP.CDGroupID, tblCheckDetailsTMP.CDSubGroupID,
tblCheckDetailsTMP.CDLineID, tblItems.ItemTypeID;"
from the first query. I see the fields but perhaps something is missing?
Thanks
DS
Me.RecordSource = "SELECT tblCheckDetailsTMP.CDCheckID,
tblCheckDetailsTMP.CDQuantity, IIf([CDSub]=1,tblItems.ItemName,IIf([CDSub]=2
Or [CDSub]=3,"" "" & tblItems.ItemName,IIf([CDSub]=4,"" "" &
tblItems.ItemName,IIf([CDSub]=5,"" "" &
tblItems.ItemName,IIf([CDSub]=6,tblItems.ItemName))))) AS NAME, " & _
"IPRICE([CDQuantity],[CDPrice],[CDDiscountAmount],[CDDiscountPercent],[CDDiscountDP],[CDDiscountWhere],[CDTaxRate],[CDInclusive])
AS PRICE, tblChecksTMP.ChkDate, tblChecksTMP.ChkTime, [ChkAlias] & "" / "" &
[ChkGuests] AS INFO, tblChecksTMP.ChkServer, tblChecksTMP.ChkDividedCheck, "
& _
"tblCheckDetailsTMP.CDGroupID, tblCheckDetailsTMP.CDSubGroupID,
tblCheckDetailsTMP.CDLineID, tblItems.ItemTypeID, tblChecksTMP.ChkMessage "
& _
"FROM (tblChecksTMP INNER JOIN tblCheckDetailsTMP ON
tblChecksTMP.CheckID = tblCheckDetailsTMP.CDCheckID) INNER JOIN tblItems ON
tblCheckDetailsTMP.CDItemID = tblItems.ItemID " & _
"WHERE (((tblCheckDetailsTMP.CDCheckID) =
[Forms]![frmCheckPreview]![TxtCheckID])) " & _
"UNION ALL " & _
"SELECT tblDiscountDetails.CDCheckID, tblDiscountDetails.CDQuantity, " &
_
"tblDiscounts.DiscountName AS NAME,
DPRICE([CDDiscountDP],[CDDiscountAmount],[CDDiscountPercent]) AS PRICE, " &
_
"tblChecksTMP.ChkDate, tblChecksTMP.ChkTime, """" AS
INFO,tblChecksTMP.ChkServer,tblChecksTMP.ChkDividedCheck, " & _
"tblCheckDetailsTMP.CDGroupID , tblCheckDetailsTMP.CDSubGroupID,
tblCheckDetailsTMP.CDLineID, tblDiscounts.DiscountName,
tblChecksTMP.ChkMessage " & _
"FROM (tblChecksTMP INNER JOIN tblDiscountDetails ON
tblChecksTMP.CheckID = tblDiscountDetails.CDCheckID) " & _
"INNER JOIN tblDiscounts ON tblDiscountDetails.CDDiscountID =
tblDiscounts.DiscountID " & _
"WHERE tblDiscountDetails.CDCheckID = Forms!frmCheckPreview!TxtCheckID "
& _
"ORDER BY tblCheckDetailsTMP.CDGroupID, tblCheckDetailsTMP.CDSubGroupID,
tblCheckDetailsTMP.CDLineID, tblItems.ItemTypeID;"