Format DatePart

[mbr96 via AccessMonster.com]

I'm trying to extract the month name (not just the number) from a ChkDate
field in a query. ChkDate is a Date/Time data type. I use a DatePart
expression which works fine to pull the month number.

MonthNum: DatePart("m",[ChkDate])

But when I try to format that to get MonthName, it return Jan for any non-
January month and Dec for any January month in ChkDate. Curious. Here's
what I'm trying, and I can't see what's wrong according to syntax in help for
Format and DatePart. Any ideas?

MonthName: Format(DatePart("m",[ChkDate]),"mmm")

Thanks in advance.
MBR

 

[John Spencer]

Try
Format([ChkDate],"mmm")

Since datepart is returning a number from 1 to 12, what you are doing is
trying to format day 1 (December 31, 1899) or day 2-12 (January 1 to 11,
1900).


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[fredg]

I'm trying to extract the month name (not just the number) from a ChkDate
field in a query. ChkDate is a Date/Time data type. I use a DatePart
expression which works fine to pull the month number.

MonthNum: DatePart("m",[ChkDate])

But when I try to format that to get MonthName, it return Jan for any non-
January month and Dec for any January month in ChkDate. Curious.

It's not curious at all.
Any month returned will be between 1 and 12.
Formatting a value between 2 and 12 as a date, will always return a
January. Formatting a 1 as a date will always return December.

Access stores dates as the number of days since Dec 30, 1899.
So the month value of Format(1,"mmm") is always December.
Format (2 through 12,"mmm") is always January, i.e. 12 days from
12/30/1899 is January 11, 1900.

Here's

what I'm trying, and I can't see what's wrong according to syntax in help for
Format and DatePart. Any ideas?

MonthName: Format(DatePart("m",[ChkDate]),"mmm")

Thanks in advance.
MBR

You should not use MonthName as a field name.
There is an Access function named "MonthName"

You can use:

TheMonth:MonthName(DatePart("m",[ChkDate]))
or ...
TheMonth:MonthName(Month([ChkDate]))
Or easier yet ....
TheMonth:Format([ChkDate],"mmmm")

 

[mbr96 via AccessMonster.com]

Thanks - I was making it more complicated than it needed to be. It's working.


Thanks again!
MBR

Try
Format([ChkDate],"mmm")

Since datepart is returning a number from 1 to 12, what you are doing is
trying to format day 1 (December 31, 1899) or day 2-12 (January 1 to 11,
1900).

I'm trying to extract the month name (not just the number) from a ChkDate
field in a query. ChkDate is a Date/Time data type. I use a DatePart

[quoted text clipped - 12 lines]

Thanks in advance.
MBR

 

[Marshall Barton]

I'm trying to extract the month name (not just the number) from a ChkDate
field in a query. ChkDate is a Date/Time data type. I use a DatePart
expression which works fine to pull the month number.

MonthNum: DatePart("m",[ChkDate])

But when I try to format that to get MonthName, it return Jan for any non-
January month and Dec for any January month in ChkDate. Curious. Here's
what I'm trying, and I can't see what's wrong according to syntax in help for
Format and DatePart. Any ideas?

MonthName: Format(DatePart("m",[ChkDate]),"mmm")

MonthName: MonthName(DatePart("m",[ChkDate]))
or
MonthName: Format(ChkDate,"mmm")

It should be obvious from the above that you need to use a
different name for the calculated field.