sum (date < now())?

[marc]

Hi,

I have an Access db where I retrieve rows :

SELECT
Shipyard,
Shipyard_Country,
Main_Vessel_type,
Count(Main_Vessel_type) AS VesselCount,
Due_Or_Delivered
FROM
tblShip
GROUP BY
Due_Or_Delivered,
Main_Vessel_type,
Shipyard_Country,
Shipyard

The column Due_Or_Delivered contains a date. I want to have in the a
resultset 2 extra columns:
a column containing a number indicating how many of the grouped rows
have a datevalue in the column Due_Or_Delivered that is equal to or
earlier than Now
and
a column containing a number indicating how many of the grouped rows
have a datevalue in the column Due_Or_Delivered that is later than
Now.

I tried

Sum(CASE Due_Or_Delivered When Due_Or_Delivered >= Now() THEN 1 ELSE 1
END CASE) AS Delivered,
Sum(CASE Due_Or_Delivered When Due_Or_Delivered < Now() THEN 1 ELSE 1
END CASE) AS OnOrder,

but Access does not know this.

How can I do this in Access?

Thanks

[marc]

Will this work in your larger query? I don't know.
Thanks, I'm moving again..