MonthDate Function

[DAWNY2007]

I am trying to do a Totals query and group on a date (mm/dd/yy) field by Month Name. It is grouping by Month number right now. I tried to use the MonthName function, but I really don't understand what should be in there. What criteria should I enter ? What does the abbreviate mean ?



MonthName(«month», «abbreviate»)

The field I am grouping on is called Actual Date

Thanks in advance !

[Bob Fitz]

See the following link for info about MonthName() function: https://docs.microsoft.com/en-us/off...hname-function

Using the following expression in the first line of a column in query design: Mth: MonthName(Month([Actual Date]),-1)
will give a column in the query, headed "Mth", that returns an abbreviated value for the name of the month in [Actual Date]
To return the name of the month in full, use: Mth: MonthName(Month([Actual Date]),0)

[DAWNY2007]

Thank you Bob (once again) !

[Bob Fitz]

I've just seen that you were also asking about using criteria.
If criteria is applied to the calculated field that I suggested earlier then it would need to be in the format as the month being returned. eg "Jan" if abbreviated. "January" id not.
Alternatively, you could have another column MthNum: Month([Actual Date]) in which the criteria needed would be the numerical value of the month. eg 1 would return January records.

PS
Thank you for your kind words in your reputation comments

EDIT:
The column being used for the criteria does not need to be shown in the dataset returned

[ArviLaanemets]

My advice is: Definitely have month number returned by query! Use month number for grouping AND FOR ORDERING (Ordering results by month name will give you monthly results disordered). You can have month name returned optionally, and display it e.g. in report instead of month number, but use it for display only!

And this is applicable only when your query will return data for a single year only! In case results for months over several year are returned, I myself as a rule use a month number as an integer in format yyyymm.