Is it possible to have a query remove current month's entries?

[templeowls]

Is there an easy way to have a query remove all entries where the entry's date is this month? Meaning it would remove all entries where the month is March and then once it turns to April, March's then appear and April's are hideen/removed

Any recommendations?

[Minty]

Assuming you have a date field and data spanning more than a year then you would probably want to ensure you exclude only this years March entries?

So something like

SELECT * FROM YourTable
WHERE (Month([YourDate]) <> Month(Date()) AND Year([YourDate]) <> Year(Date()))

[templeowls]

I have a monthnumber field. Is it possible to do this off of that?

[CJ_London]

I have a monthnumber field. Is it possible to do this off of that?

only if your data never holds more than 12 months, or your month number contains a year element to identify which year the month refers to

[templeowls]

The data is only ever the current year

[Gicu]

So Month(Date()) returns the number of the current month, therefore to eliminate those records you would add <>Month(Date()) in the criteria row of your month field.

Cheers,

[templeowls]

So I'm using the following to populate the month number:

MonthNumber: Format([MyDate],"mm")

So what exactly would I put in the criteria to exclude the current month?

[CJ_London]

So what exactly would I put in the criteria to exclude the current month?

format([MyDate],"mm")<>Format(Date,"mm")

or

month([MyDate])<>month(date)

[templeowls]

The query is asking me to input something 'date' whenever I run the query. For both solutions

[CJ_London]

use date(), not just date

[templeowls]

So i used the following code:

MonthNumber: Format([MyDate],"mm")<>Format(Date(),"mm")

All that does is show January and February dates as -1, and March as 0

I'm looking for jan to be 1, feb to be 2, and march to filter out altogethr (until we go into April)

[CJ_London]

without seeing your sql it is anyone's guess. You haven't followed the suggestions made

[Gicu]

You don't write it like that... In the first row of the query designer you add: MonthNumber: Format([MyDate],"mm"). Then in that new calculated field you go to the Criteria row underneath and add: <>Format(Date(),"mm").

Or you leave it as you have it (MonthNumber: Format([MyDate],"mm")<>Format(Date(),"mm")) and add <>0 (which equals False) or you simply put True (=-1) in the criteria row.

Cheers,

[templeowls]

You don't write it like that... In the first row of the query designer you add: MonthNumber: Format([MyDate],"mm"). Then in that new calculated field you go to the Criteria row underneath and add: <>Format(Date(),"mm").

Or you leave it as you have it (MonthNumber: Format([MyDate],"mm")<>Format(Date(),"mm")) and add <>0 (which equals False) or you simply put True (=-1) in the criteria row.

Cheers,

That worked!! Thank you!

[Gicu]