sum (date < now())?

[marc]

Hi,

I have an Access db where I retrieve rows :

SELECT
Shipyard,
Shipyard_Countr y,
Main_Vessel_typ e,
Count(Main_Vess el_type) AS VesselCount,
Due_Or_Delivere d
FROM
tblShip
GROUP BY
Due_Or_Delivere d,
Main_Vessel_typ e,
Shipyard_Countr y,
Shipyard

The column Due_Or_Delivere d 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_Delivere d 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_Delivere d that is later than
Now.

I tried

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

but Access does not know this.

How can I do this in Access?

Thanks

[marc]

Re: sum (date &lt; now())?

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