Can't sort by expression

G

Guest

The following query is not sorting as required. I am adding up cases shipped
out within date parameters and would then like to sort by number of cases
descending. Any help is appreciated.

SELECT Items.Environment, Items.Item, Last(Items.Description) AS
LastOfDescription, Customers.CustomerID, Customers.Name,
nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1 And
[actualpickupdate]<DateAdd("d",1,Forms!FrmItemMovmtParameters!Date2),[qtycases])),0)
AS CasesOut, Customers.InactiveCust,
nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1 And
[actualpickupdate]<DateAdd("d",1,Forms!FrmitemmovmtParameters!Date2),[qtypallets])),0) AS PalletsOut
FROM (Customers LEFT JOIN Items ON Customers.CustomerID = Items.CustomerID)
LEFT JOIN (BillsOfLading RIGHT JOIN InventoryCurrent ON
BillsOfLading.BillOfLadingID = InventoryCurrent.BillOfLadingID) ON
Items.ItemID = InventoryCurrent.ItemID
GROUP BY Items.Environment, Items.Item, Customers.CustomerID,
Customers.Name, Customers.InactiveCust
ORDER BY nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1
And
[actualpickupdate]<DateAdd("d",1,Forms!FrmItemMovmtParameters!Date2),[qtycases])),0) DESC;

Thanks, Alison.
 
G

Guest

Try omitting --
nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1 And
[actualpickupdate]<DateAdd("d",1,Forms!FrmitemmovmtParameters!Date2),[qtypallets])),0) AS PalletsOut
 
G

Guest

Thanks for responding Karl, but that didn't work. Anyway I need to show the
Pallets out.

Any other ideas?

Thanks

KARL DEWEY said:
Try omitting --
nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1 And
[actualpickupdate]<DateAdd("d",1,Forms!FrmitemmovmtParameters!Date2),[qtypallets])),0) AS PalletsOut


AlienzDDS said:
The following query is not sorting as required. I am adding up cases shipped
out within date parameters and would then like to sort by number of cases
descending. Any help is appreciated.

SELECT Items.Environment, Items.Item, Last(Items.Description) AS
LastOfDescription, Customers.CustomerID, Customers.Name,
nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1 And
[actualpickupdate]<DateAdd("d",1,Forms!FrmItemMovmtParameters!Date2),[qtycases])),0)
AS CasesOut, Customers.InactiveCust,
nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1 And
[actualpickupdate]<DateAdd("d",1,Forms!FrmitemmovmtParameters!Date2),[qtypallets])),0) AS PalletsOut
FROM (Customers LEFT JOIN Items ON Customers.CustomerID = Items.CustomerID)
LEFT JOIN (BillsOfLading RIGHT JOIN InventoryCurrent ON
BillsOfLading.BillOfLadingID = InventoryCurrent.BillOfLadingID) ON
Items.ItemID = InventoryCurrent.ItemID
GROUP BY Items.Environment, Items.Item, Customers.CustomerID,
Customers.Name, Customers.InactiveCust
ORDER BY nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1
And
[actualpickupdate]<DateAdd("d",1,Forms!FrmItemMovmtParameters!Date2),[qtycases])),0) DESC;

Thanks, Alison.
Click to expand...
Click to expand...
 
G

Guest

I have figured out a solution.

I have changed the query to an append query. Apended to an empty table and
use that table as the data source for the report.

Now I just need to have the code skip over the append query warnings.

Thanks anyway.

KARL DEWEY said:
Try omitting --
nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1 And
[actualpickupdate]<DateAdd("d",1,Forms!FrmitemmovmtParameters!Date2),[qtypallets])),0) AS PalletsOut


AlienzDDS said:
The following query is not sorting as required. I am adding up cases shipped
out within date parameters and would then like to sort by number of cases
descending. Any help is appreciated.

SELECT Items.Environment, Items.Item, Last(Items.Description) AS
LastOfDescription, Customers.CustomerID, Customers.Name,
nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1 And
[actualpickupdate]<DateAdd("d",1,Forms!FrmItemMovmtParameters!Date2),[qtycases])),0)
AS CasesOut, Customers.InactiveCust,
nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1 And
[actualpickupdate]<DateAdd("d",1,Forms!FrmitemmovmtParameters!Date2),[qtypallets])),0) AS PalletsOut
FROM (Customers LEFT JOIN Items ON Customers.CustomerID = Items.CustomerID)
LEFT JOIN (BillsOfLading RIGHT JOIN InventoryCurrent ON
BillsOfLading.BillOfLadingID = InventoryCurrent.BillOfLadingID) ON
Items.ItemID = InventoryCurrent.ItemID
GROUP BY Items.Environment, Items.Item, Customers.CustomerID,
Customers.Name, Customers.InactiveCust
ORDER BY nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1
And
[actualpickupdate]<DateAdd("d",1,Forms!FrmItemMovmtParameters!Date2),[qtycases])),0) DESC;

Thanks, Alison.
Click to expand...
Click to expand...
 
J

John Vinson

I have figured out a solution.

I have changed the query to an append query. Apended to an empty table and
use that table as the data source for the report.

Now I just need to have the code skip over the append query warnings.
Click to expand...

That's a really ugly solution...

Why not base another SELECT query on the totals query, and put in an
OrderBy for the calculated field, if you can't get the calculated
field to sort in a single query?

John W. Vinson[MVP]
 
G

Guest

Thanks John,

I know it's an ugly solution. Thanks for your suggestion. I'll give it a
try.

Ali
 
Top