Filtering by month() in ACCESS
Solved
JV-Vierzon
-
Jean_Jacques Posted messages 1045 Registration date Status Member Last intervention -
Jean_Jacques Posted messages 1045 Registration date Status Member Last intervention -
Bonjour,
In Access, in a query, I want to filter dates using the Month() and Year() functions.
I have a "Date" field, and I created 2 fields:
- Year: Year([Date]) with filter condition [Year :]
- Month: Month([Date]) with filter condition <[Month number ?]
When running this query, the filtering works for the year (records for the year entered in the question [Year:]) but not for the month: for example, entering 2 (February) shows me records from January (good) but also those from October (10), November (11), and December (12);
As if Month() were an alphanumeric value...
With [Month number ?] (equal), it works.
Where is the error?
Thanks in advance.
In Access, in a query, I want to filter dates using the Month() and Year() functions.
I have a "Date" field, and I created 2 fields:
- Year: Year([Date]) with filter condition [Year :]
- Month: Month([Date]) with filter condition <[Month number ?]
When running this query, the filtering works for the year (records for the year entered in the question [Year:]) but not for the month: for example, entering 2 (February) shows me records from January (good) but also those from October (10), November (11), and December (12);
As if Month() were an alphanumeric value...
With [Month number ?] (equal), it works.
Where is the error?
Thanks in advance.
4 answers
-
Good evening JV-Vierzon,
By adapting this request, you will work solely on the date field.
Therefore, if you enter: 12 as the month's ordinal and: 2010 as the vintage, you will directly list the records from December 2010.
SELECT TRANSACTIONS.DateSaisie, TRANSACTIONS.RéfValeur, DatePart("m",[Datesaisie]) AS Month, DatePart("yyyy",[Datesaisie]) AS Vintage
FROM TRANSACTIONS
WHERE (((DatePart("m",[Datesaisie]))=[ What month ordinal? ]) AND ((DatePart("yyyy",[Datesaisie]))=[ Vintage? ]))
ORDER BY DatePart("m",[Datesaisie]);
Best regards
--
Science only discovers what has always existed.
REEVES Hubert.-
Hello Jean-Jacques
Thank you for the quick response.
However, I still have the same issue: this query works well for searching records for a given month:
SELECT [26_01_ProduitsColis].N°Annuaire, [26_01_ProduitsColis].Date, DatePart("m",[Date]) AS Month
FROM 26_01_ProduitsColis
WHERE (((DatePart("m",[Date]))=[ Month number ? ]) AND ((DatePart("yyyy",[Date]))=[ Year ? ]))
ORDER BY DatePart("m",[Date]);
But to filter records from previous months:
SELECT [26_01_ProduitsColis].N°Annuaire, [26_01_ProduitsColis].Date, DatePart("m",[Date]) AS Month
FROM 26_01_ProduitsColis
WHERE (((DatePart("m",[Date]))<[ Month number ? ]) AND ((DatePart("yyyy",[Date]))=[ Year ? ]))
ORDER BY DatePart("m",[Date]);
For a year value of 2 (February), the records for months 2 to 9 are indeed blocked, but not for months 10, 11, and 12.
These numbers, which are the month values, are numeric (?) and 10 to 12 should not be considered as <2.
The filter seems to be alphabetical, treating these values as strings: "2" is indeed greater than "10", "11", and "12" which start with 1.
Thank you in advance for your insights.
Jean -
Hello
Here is an example of a parameter query
where you specify the choice of month and year
SELECT TRANSACTIONS.Datesaisie, DatePart("m",[Datesaisie]) AS Month, DatePart("yyyy",[Datesaisie]) AS Years
FROM TRANSACTIONS
WHERE (((DatePart("m",[Datesaisie]))=[Which Month]) AND ((DatePart("yyyy",[Datesaisie]))=[Which Year]))
ORDER BY DatePart("m",[Datesaisie]);
-
-
Hello JV-Vierzon,
For lack of a better option, here is an approximate solution.
It appears that the argument <= is strictly respected, while, strangely, < used alone is not......
I leave it to you to appreciate, or not...
SELECT TRANSACTIONS.DateSaisie, TRANSACTIONS.RéfValeur, DatePart("m",[Datesaisie]) AS Month, DatePart("yyyy",[Datesaisie]) AS Year
FROM TRANSACTIONS
WHERE (((DatePart("m",[Datesaisie]))<=[ Tenth of the month? ]) AND ((DatePart("yyyy",[Datesaisie]))=[ Year ? ]));
Best regards
--
Science only finds what has always existed.
REEVES Hubert. -
Hello JV-Vierzon,
Purists might not appreciate it very much, but ... it works ...
- By inserting - 1 after -> [ Month's Nth ? ]-1
The query lists records prior to the value of the entered month's Nth ...
SELECT TRANSACTIONS.DateSaisie, TRANSACTIONS.RéfValeur, DatePart("m",[Datesaisie]) AS Month, DatePart("yyyy",[Datesaisie]) AS Year
FROM TRANSACTIONS
WHERE (((DatePart("m",[Datesaisie]))<=[ Month's Nth ? ]-1) AND ((DatePart("yyyy",[Datesaisie]))=[ Year ? ]))
ORDER BY DatePart("m",[Datesaisie]);
Sincerely -
Hello Jean-Jacques.
Thank you for your help which assisted me in this research. The following solution works:
SELECT [26_01_ProduitsColis].N°Annuaire, [26_01_ProduitsColis].Date, DatePart("m",[Date]) AS Month, DatePart("yyyy",[Date]) AS Year
FROM 26_01_ProduitsColis
WHERE (((DatePart("m",[Date]))<Val([Month number:])) AND ((DatePart("yyyy",[Date]))=[Year:]))
ORDER BY DatePart("m",[Date]);
Using the VAL() function forces the numeric conversion of my data in [Month number:].
I think it’s equivalent to the -1 suggested (a solution I’ve already used in Excel when copy-pasting to force the values to be numeric: Special paste/Multiply in a column filled with 1).
What’s strange is that the value, always a digit, entered in the variable [Month number:] is considered, by default, as a string by ACCESS and that it can compare it to a normally numeric value: Month().
My return to the databases I left a few years ago (DBase on Amstrad) is quite laborious.
However, there was no Internet and forums like yours back then, which are a great help.
Thanks to those who give their time to help us.
Best regards.
JV-Vierzon-
Hello JV-Vierzon,
Val() : I feel guilty, it's so obvious!
Regarding AMSTRAD, the legendary manager from the great era, Marion VANNIER, now lives off her investments(!). She talks about the heroic beginnings of the brand as well as the rather discreet end of this company in reports.
I've also known DBASE IV. At the time, I thought there would never be anything better.... Just like with AMSTRAD, by the way.....
Conclusion: We live in interesting times
Have a good day.
-
