Format month number as text

[GelC]

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:

=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!

 

[SkipVought]

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]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

Skip's correct. If you don't have a real date, you can use the MonthName() function in most versions of Access.

=MonthName([Month]) & " " & [Year]

Duane
Hook'D on Access
MS Access MVP

 

[GelC]

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

 

[GelC]

Thanks Duane! Great!!!

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

 

[SkipVought]

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]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

This expression creates a real date. You can set the format of a text box to display it as September 2009.

  =DateSerial([Year], [Month],1)

Duane
Hook'D on Access
MS Access MVP

 

[alvechurchdata]

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

'-- 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

http://www.alvechurchdata.co.uk

 

[dhookom]

It looks like there are two separate fields which are not well named. Both Month and Year are functions and should not be used as names in Access.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya GelC . . .

If you use the date the month was derived from, you could simply use:

[blue]   Format([i][[purple][b]YourDateFieldName[/b][/purple]][/i],"mmm yyyy")[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]