change month as number to month as full name

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I need to convert a value of 1 to 12 to display the corresponding full name
of the month. ie 1 as january etc. I have tried formatdate( value, "mmmm")
but doesn't work pretty sure there must be an easy solution for this.

Thanks
Chris
 
I need to convert a value of 1 to 12 to display the corresponding full name
of the month. ie 1 as january etc. I have tried formatdate( value, "mmmm")
but doesn't work pretty sure there must be an easy solution for this.

Thanks
Chris
Click to expand...

A couple:

Switch([Monthno], "January", "February", "March", "April", ...)

or

Format(DateSerial(2000, [Month], 1), "mmmm")

Quiz for the curious: explain why Format(value, "mmmm") returns
December for 1 and January for 2 through 12. Prize is one free answer
in the newsgroup...<g>
 
John,

When I use
Format([datezz],"mmmm")
I get the right months for the corresponding digit. What
do you mean that December is the value for "1"
Access 2000

fortunately there are people like you out there that give
great answers for free.

thanks

Jim
-----Original Message-----
I need to convert a value of 1 to 12 to display the corresponding full name
of the month. ie 1 as january etc. I have tried formatdate( value, "mmmm")
but doesn't work pretty sure there must be an easy solution for this.

Thanks
Chris
Click to expand...

A couple:

Switch([Monthno], "January", "February", "March", "April", ....)

or

Format(DateSerial(2000, [Month], 1), "mmmm")

Quiz for the curious: explain why Format(value, "mmmm") returns
December for 1 and January for 2 through 12. Prize is one free answer
in the newsgroup...<g>


.
Click to expand...
 
Chris,

the easiest way to do this is to create a separate table (Month_Lookup
where you create two fields (Month_Code, Month_Desc). For eac
month_Code input values 1>12 and enter their corresponding description
in the Month_Desc field.

Once this is done simply link Month_Lookup to your other table whic
shows the month codes and place Month_Desc in your query.

Problem solved and you can reuse the lookup over and over again i
different queries.

Alternatively you can use the monthName function in Access as below:
MonthName([Month_No]) where Month_No is the field in your table tha
contains the various month numbers.

Hope this help
 
Jim/Chris said:
Quiz for the curious: explain why Format(value, "mmmm") returns
December for 1 and January for 2 through 12. Prize is one free answer
in the newsgroup...<g>
Click to expand...

John,

When I use
Format([datezz],"mmmm")
I get the right months for the corresponding digit. What
do you mean that December is the value for "1"
Access 2000

fortunately there are people like you out there that give
great answers for free.

thanks
Click to expand...

I won't steal John's thunder, but I'll give you a hint.

See what you get with Format(1, "dd mmm yyyy")
 
Hello:

I notice that:

Format(Value, "mmmm") also returns "December".

Value= -29 through 0 also returns December ....
Value=2 through 32 returns January (32-1) January has 31 days?
Value= 33 through 60 returns February (60-32=28) February has 28 days?

Using Doug's hint:

Format(Value, "dd mmm yyyy")

Value= -1 returns 29 Dec 1899
Value= 0 returns 30 Dec 1899
Value= 1 returns 31 Dec 1899
Value= 2 returns 01 Jan 1900
Value=367 returns 01 Jan 1901

So it's the number of days counting off from (zero based?) from December 30,
1899.. Correct so far? But *WHY*.. I still don't have a clue! I'm brave
enough to look stupid... this is as far as I've gotten...

Fred Boer

P.S. You are driving me to use Access Help - I hope you feel badly about
that! :(
P.P.S. And I *don't* have time for this! <g>


Ok, well this is not good. I am *really* busy, but I can't stop
Douglas J. Steele said:
Jim/Chris said:
Quiz for the curious: explain why Format(value, "mmmm") returns
December for 1 and January for 2 through 12. Prize is one free answer
in the newsgroup...<g>
Click to expand...

John,

When I use
Format([datezz],"mmmm")
I get the right months for the corresponding digit. What
do you mean that December is the value for "1"
Access 2000

fortunately there are people like you out there that give
great answers for free.

thanks
Click to expand...

I won't steal John's thunder, but I'll give you a hint.

See what you get with Format(1, "dd mmm yyyy")
Click to expand...
 
John,

When I use
Format([datezz],"mmmm")
I get the right months for the corresponding digit. What
do you mean that December is the value for "1"
Access 2000

fortunately there are people like you out there that give
great answers for free.
Click to expand...

Formatting a *date field* will give you the right month (Format
wouldn't be much use if it didn'!)

Formatting a *month number* such as 1, 3, or 12 will in fact do as I
posted. Try it.
 
My understanding is that Microsoft deliberately perpetuated the error Lotus
made with Lotus 1-2-3 (they thought 1900 was a leap year) when they brought
out Excel to minimize issues converting to Excel. Once something like that
gets embedded in the code, it's there for life!
 
John

I'm going to venture a response (guess, speculation, display of ignorance, etc.) to your challenge - there are two parts to the answer. Part one: The format(value,"mmmm") returns "January" or "December" for 2,3, or 1 respectively because there are 4 emmms ("mmmm"). 3 emmms ("mmm") returns an abbreviation ("Jan" or "Dec"), 2 emms ("mm") returns a numeric value with a leading zero ("01", "12") and 1 emm ("m") returns a numeric value without a leading zero ("1","12").

Part 2 is less about Access and more about Windows. The 'fix' for the Y2K bug specified that December 31, 1899 has a value of "1", January 01,1900 has a value of "2", etc. I speculate that when MS was working out the solution to the Y2K bug, the code writers were initially concerned with making certain that a two-digit year entry would be recorded as being in the current century. So for testing purposes they had only specified a range broad enough to span more than one century (12/31/1899 - 01/02/2000). Only after making the code work correctly did they then add the negative numbers

Rick..

----- John Vinson wrote: ----

On Wed, 3 Mar 2004 22:28:33 -0800, "Chris
I need to convert a value of 1 to 12 to display the corresponding full nam
of the month. ie 1 as january etc. I have tried formatdate( value, "mmmm"
but doesn't work pretty sure there must be an easy solution for this
Chri
Click to expand...


A couple

Switch([Monthno], "January", "February", "March", "April", ...

or

Format(DateSerial(2000, [Month], 1), "mmmm"

Quiz for the curious: explain why Format(value, "mmmm") return
December for 1 and January for 2 through 12. Prize is one free answe
in the newsgroup...<g

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi
 
Part 2 is less about Access and more about Windows. The 'fix' for the Y2K bug specified that December 31, 1899 has a value of "1", January 01,1900 has a value of "2", etc. I speculate that when MS was working out the solution to the Y2K bug, the code writers were initially concerned with making certain that a two-digit year entry would be recorded as being in the current century.
Click to expand...

VERY close. In fact (as noted elsethread) the December 30, 1899 zero
point harks back much further, to the 1970's when Lotus 1-2-3 was
developed. That program's developers apparently made the mistake of
assuming that 1900 was a leap year (2000 was because it's divisible by
400, but 1900 wasn't); they were trying to make January 1, 1900 into
Day 1, but were off by a day. Microsoft chose to perpetuate this error
to provide compatibility (and an easier transition path) for Lotus.
 
Fascinating... so does Doug get the free answer in the newsgroup? I'm not
sure he needs the help, but, you never know.... <g>

Fred
 
Fascinating... so does Doug get the free answer in the newsgroup? I'm not
sure he needs the help, but, you never know.... <g>
Click to expand...

I'll buy him a drink next time we get together instead... I am NOT
going to offer to teach Doug anything about Access!!! <g>
 
That seems to be overkill for something that can be done with a single
statement in code!

As has been pointed out, Format(DateSerial(2004, MonthNumber, 1), "mmmm")
will do it.
 
John

Thank you for the explanation and for the courtesy of the response

Rick..

----- John Vinson wrote: ----

On Thu, 4 Mar 2004 15:31:05 -0800, Rick Willingha
Part 2 is less about Access and more about Windows. The 'fix' for the Y2K bug specified that December 31, 1899 has a value of "1", January 01,1900 has a value of "2", etc. I speculate that when MS was working out the solution to the Y2K bug, the code writers were initially concerned with making certain that a two-digit year entry would be recorded as being in the current century
Click to expand...

VERY close. In fact (as noted elsethread) the December 30, 1899 zer
point harks back much further, to the 1970's when Lotus 1-2-3 wa
developed. That program's developers apparently made the mistake o
assuming that 1900 was a leap year (2000 was because it's divisible b
400, but 1900 wasn't); they were trying to make January 1, 1900 int
Day 1, but were off by a day. Microsoft chose to perpetuate this erro
to provide compatibility (and an easier transition path) for Lotus

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi
 
Back
Top