query for dates

[Ra]

Hi,

I have the following data:

CSPC Exp date

B0893 11/19/2009
B0893 11/19/2010

B0894 10/20/2009
B0894 11/18/2010

B0906 11/13/2013

I need to extract only the CSPC’s that show current year (where I have two
instances), and the CSPC that shows another date greater than current year.

I should get a query with the following data:

B0893 11/19/2009
B0894 10/20/2009
B0906 11/13/2013

Any suggestions would be greatly appreciated.

Thank you,

 

[vanderghast]

Can we 'reformulate' your requirements to:

For each CSPC, return the MINIMUM date among all the dates, for that CSPC,
which occured at of after the January first of this year?

If so, try:


SELECT cspc, MIN([exp date])
FROM tableNameHere
WHERE [exp date] >= DateSerial( Year(now), 1, 1)
GROUP BY cspc


Vnaderghast, Access MVP

 

[KARL DEWEY]

Try these --
CSPC_Count --
SELECT CSPC.CSPC, Count(CSPC.CSPC_Date) AS CountOfCSPC_Date1,
Sum(IIf(Year([CSPC_Date])>Year(Date()),1,0)) AS Expr1
FROM CSPC
GROUP BY CSPC.CSPC;

SELECT CSPC.CSPC, CSPC.CSPC_Date AS DateA
FROM CSPC INNER JOIN CSPC_Count ON CSPC.CSPC = CSPC_Count.CSPC
WHERE (((CSPC.CSPC_Date)<DateSerial(Year(Date())+1,1,1)) AND
((CSPC_Count.CountOfCSPC_Date1)>1) AND ((CSPC_Count.Expr1)>0)) OR
(((CSPC.CSPC_Date)>=DateSerial(Year(Date())+1,1,1)) AND
((CSPC_Count.CountOfCSPC_Date1)=1) AND ((CSPC_Count.Expr1)=1));