Compering number to a current month in MS Access

Question

I have a data table that looks like this:

Id	Month	Number
e1	9	2
e2	10	3
e3	10	1
e4	9	3

The month column is an integer. I want to get a sum of number both for October and September, but by using a reference to a current month and last month, as if the data is going to change in time.

I've tried using MONTH(DATE()):

SELECT Sum(dt.Number)
FROM dt
WHERE dt.Month = MONTH(DATE()); 

as well as

WHERE CInt(dt.Month) = CInt(MONTH(DATE()));

But it didn't work (it's not error, it just don't return anything). I will be greatful for any ideas.

Answer 1

Try this:

SELECT Sum(dt.Number) AS SumOfNumber
FROM dt
HAVING (((dt.Month)=Month(Date())));