Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Format month number as text

Status
Not open for further replies.

GelC

Technical User
Oct 3, 2006
93
US
Hi all,

I stored month as number and wanted to display it as text.
I have a text box called "MonthYear" on form and report to display month (as text) and year.
Here is my format:
Code:
=Format([Month],"mmmm" & " " & [Year])
My problem is the month show "January" for most records (I have over 600 records), few showing "December".
Can anyone tell me what was wrong?
Thanks!
 



hi,

The Format function converts a DATE value to year, month day etc. NOT a MONTH number. the month numbers 1 to 12 ARE valid DATE values. HOWEVER, the DATES that those values represent are 1900/1/1 to 1900/1/12 (yyyy/mm/dd)

Start with a REAL DATE!

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
I know the date showing wasn't real date.

If I do this
Control Source: =[Month] & " " & [Year]
Then the real date is showed as 11 2008, 5 2005, etc...

How can I make it shown as November 2008, May 2005 instead.
Thanks!

Gelc
 
Thanks Duane! Great!!!

I didn't see your solution while I made I own post.

 


11 2008 is NOT a real date. It is merely a STRING. Na not a very useful string at that, since it could not possibly be collated into a Date sequence.

Real dates are NUMBERS, and represent a YEAR, MONTH & DAY

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
If you're stuck with the "11 2008" format for dates because it's a legacy system then you'll just have to take the date to bits and analyse it.

Find the space

Get the characters before the space
Convert them to a number
Use the MonthName function to get that month's name.

Get the characters after the space as the year

Concatenate the month and year.

The code might look something like this
Code:
'-- Assuming d is of the form "11 2008"
pos = instr(d, " ")
m = monthname(val(left(d, pos)))
y = mid(d, pos + 1)
? m & " " & y


Geoff Franklin
 
How are ya GelC . . .

If you use the date the month was derived from, you could simply use:
Code:
[blue]   Format([i][[purple][b]YourDateFieldName[/b][/purple]][/i],"mmm yyyy")[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top