Strange Month and Format results

I have a report where I need to change a label caption depending on
conditions. I have done this in the past. However, I am getting strange
results and don't know why. Strange results from immediate window below.

? Month(Forms!frmCertifedVendorForm.ReportEndDate)
12
? Format(Month(Forms!frmCertifedVendorForm.ReportEnd Date), "mmm")
Jan

How can the 12th month format as Jan? Am I stupid?

tkosel,

I imagine all the months will say *Jan* because you can't use the Month
Function like that. It will always return a numeric variable. Try...

Format(Forms!frmCertifedVendorForm.ReportEndDate, "mmm")


--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"tkosel" wrote in message
news
I have a report where I need to change a label caption depending on
conditions. I have done this in the past. However, I am getting strange
results and don't know why. Strange results from immediate window below.

? Month(Forms!frmCertifedVendorForm.ReportEndDate)
12
? Format(Month(Forms!frmCertifedVendorForm.ReportEnd Date), "mmm")
Jan

How can the 12th month format as Jan? Am I stupid?

On Sat, 2 Jan 2010 09:05:01 -0800, tkosel wrote:

I have a report where I need to change a label caption depending on
conditions. I have done this in the past. However, I am getting strange
results and don't know why. Strange results from immediate window below.

? Month(Forms!frmCertifedVendorForm.ReportEndDate)
12
? Format(Month(Forms!frmCertifedVendorForm.ReportEnd Date), "mmm")
Jan

How can the 12th month format as Jan? Am I stupid?

Perhaps a better understanding of how Access stores and processes
dates might help.

Access stores and processes Date values as the count of the number of
days since 12/30/1899.
Today's date (1/1/2010) would be stored as 40180.

Let's assume the date value in [ReportEndDate] is 12/20/2009.

Therefore
Month([ReportEndDate]) returns 12 (the 12th month ... December).
So substituting the value of Month([ReportEndDate]) in the your next
expression you have

Format(12,"mmm") which will correctly the 1st month, January.
Why? Your expression is formatting the number 12 (meaning +12 days
from 12/30/1899) which, as a date, is 1/11/1900.

So:
Format(#1/11/1900#,"mmm") does indeed return Jan.

The simplest method to return the Month as a word value is to just
use:
Format(Forms!frmCertifedVendorForm.ReportEndDate," mmm")

I hope this helps.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail