Compering number to a current month in MS Access-CodePudding

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 refference 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.

CodePudding user response：

Try this:

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

CodePudding user response：

You can use In the current month or the previous month:

Select 
    Sum([Number]) As Total
From 
    dt
Where 
    dt.Month In (Month(Date(), (Month(Date())   12 - 2) Mod 12   1);