Crosstabquery to fill a report....

[Guest]

My report is based on a crosstab query, and grouped by date ordered. When I
try to search for a specific date order, and one of the colums is missing, I
get an error...

This is the sql of my query

TRANSFORM Count(Table2.dateOrder) AS CountOfdateOrder
SELECT Table2.itemsname, Table2.dateOrder
FROM Table2
GROUP BY Table2.itemsname, Table2.dateOrder
ORDER BY Table2.dateOrder
PIVOT Switch([Table2]![DateShipped]-[table2]![DateOrder]<=2,"Shipped<=24
hrs",[Table2]![DateShipped]-[table2]![DateOrder]>2,"Shipped>48
hrs",True,"Unknown");


My report kind of looks like this

Date ordered
Item Name Shipped <=48 hrs Shipped > 48 Unknown



so if there was only one order on 09/01/05 and shipped the following day,
there is nothing shipped > 48 hrs and nothing under underknown. So I get an
error when trying to search by that date.


How will solve this please help!

 

[Duane Hookom]

Try SQL of:

TRANSFORM Count(Table2.dateOrder) AS CountOfdateOrder
SELECT Table2.itemsname, Table2.dateOrder
FROM Table2
GROUP BY Table2.itemsname, Table2.dateOrder
ORDER BY Table2.dateOrder
PIVOT Switch([Table2]![DateShipped]-[table2]![DateOrder]<=2, "Shipped<=24
hrs", [Table2]![DateShipped]-[table2]![DateOrder]>2, "Shipped>48
hrs",True,"Unknown") IN ("Shipped<=24 hrs", "Shipped>48 hrs","Unknown");

 

[Guest]

Thanks alot, that worked like magic!

Try SQL of:

TRANSFORM Count(Table2.dateOrder) AS CountOfdateOrder
SELECT Table2.itemsname, Table2.dateOrder
FROM Table2
GROUP BY Table2.itemsname, Table2.dateOrder
ORDER BY Table2.dateOrder
PIVOT Switch([Table2]![DateShipped]-[table2]![DateOrder]<=2, "Shipped<=24
hrs", [Table2]![DateShipped]-[table2]![DateOrder]>2, "Shipped>48
hrs",True,"Unknown") IN ("Shipped<=24 hrs", "Shipped>48 hrs","Unknown");

--
Duane Hookom
MS Access MVP
--

My report is based on a crosstab query, and grouped by date ordered. When
I
try to search for a specific date order, and one of the colums is missing,
I
get an error...

This is the sql of my query

TRANSFORM Count(Table2.dateOrder) AS CountOfdateOrder
SELECT Table2.itemsname, Table2.dateOrder
FROM Table2
GROUP BY Table2.itemsname, Table2.dateOrder
ORDER BY Table2.dateOrder
PIVOT Switch([Table2]![DateShipped]-[table2]![DateOrder]<=2,"Shipped<=24
hrs",[Table2]![DateShipped]-[table2]![DateOrder]>2,"Shipped>48
hrs",True,"Unknown");


My report kind of looks like this

Date ordered
Item Name Shipped <=48 hrs Shipped > 48 Unknown



so if there was only one order on 09/01/05 and shipped the following day,
there is nothing shipped > 48 hrs and nothing under underknown. So I get
an
error when trying to search by that date.


How will solve this please help!