Comparing dates in SQL

[abouddan]

HI all
I have a table in Access2000 that contains many fields and of them is a date.
I want to built a query that gives me for each client the last record in the last month.
for exemple let's say the "Table1" table contains ID (as Primary key),ClientID ( as foreign key), cDate (as Date), Amount(as Number)

ID ClientID cDate Amount

1 joe 1/1/2007 100
2 joe 1/17/2007 300
3 ken 3/1/2007 1000
4 ken 3/20/2007 700
5 joe 4/15/2007 500
6 ken 4/19/2007 800


So what I want as result is:
2 joe 1/17/2007 300
4 ken 3/20/2007 700

The last record of each client done before the current month.

thanks

[MMcCarthy]

Try this ...

SELECT First(ID), ClientID, Max(cDate), First(Amount)
FROM Table1
GROUP BY ClientID
WHERE Month(cDate) < Month(Date());
 

Mary

[abouddan]

Thank you Mary For your support.

But this query is not answering my demands , there is something wrong :

- Let's say we are in January, How can this query give me all the records that have the month of the date < January. It's as you are saying give me all the records in witch the month=0.

- Let's say we are in 03/03/2007. And the last record done by Joe is 31/12/2006. This query can't be working here, because the month of the date of the last record is 12 and it is greater then 3. You said "Where month(cDate) < Month(Date())" and 12 is greater then 3.

[MMcCarthy]

Try should fix it ...

SELECT First(ID), ClientID, Max(cDate), First(Amount)
FROM Table1
GROUP BY ClientID
WHERE cDate < DateSerial(Year(Date()), Month(Date()), 1)
 

Mary

[abouddan]

Thanks Mry I think that's it
I am testing this function right now but I think It's OK

Many Thanks