Format DatePart to show the month in words

[Dumferling]

I have this:
SortByMonth: Format(DatePart("m",[DatePublished]),"mmmm")
DatePart gives me a number for the month but I want to show that as a name. The above defaults everything to January so that I get:

rather than the words for each of the months.

Is there another way to do this?

 

[ebs17]

Format(DatePublished, "mmmm")

But the following sorting is probably much different than you expect.

 

[Dumferling]

Format(DatePublished, "mmmm")

But the following sorting is probably much different than you expect.

OK, I see. That works if I put it in a different colum. I was trying to convert the DatePart but that was overcomplicating it. Thank you.

 

[ebs17]

Format(XXX, "mmmm")

... expects a valid date for XXX, the numbers from DatePart are reinterpreted accordingly:

? CDate(1), CDate(2), CDate(12)
31.12.1899    01.01.1900    11.01.1900

 

[Pat Hartman]

Do NOT format date parts prior to sorting. You probably don't want your months in alpha order and if you supress the leading zeros, your numeric months will not sort correctly either. And then there is the problem that arises when you have more than a single year in your recordset. For example Nov, 2022 - May, 2023 - the months won't sort correctly there either.

Just sort on the actual date field and create a formatted field used for display - although, I still don't format in a query unless I need to do that because I am exporting to Excel or some other application. All formatting is best done in forms/reports EXCEPT/UNLESS you want to sort on the formatted value.