Skip to content
Advertisement

Compering number to a current month in MS Access

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.

Advertisement

Answer

Try this:

SELECT Sum(dt.Number) AS SumOfNumber
FROM dt
HAVING (((dt.Month)=Month(Date())));
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement