Convert month number to name (1 makes December?)

[Rik_StHelens]

Hi

I have a report which displays the 2 query parameters used to generate it.

The user enters a month number and year number.

I have used the following statement:

Format([Reports]![MyReport]![Month Number], "mmmm"))

When i pull off a report for January 2010 I get the results i expect, but the Month Number "1" is converted to December.

I try using "01" as a parameter to represent January and I get no results.

What could be causing it to give me the wrong month name?

Many Thanks

 

[JamesMcS]

Could it be something to do with data types? Entering "01" and getting no results suggests the month number is in fact a string (if it was a number 1 and 01 would be treated in the same way, I think). That's the only thing I can think of....

 

[vbaInet]

Try this first:

MonthName([Reports]![MyReport]![Month Number])

 

[gemma-the-husky]

this is often a problem with internal company years, that are different from calendar years. Access has lots of functions for handling true DATES, but anythnig else (eg number or text fields representing dates) cant be managed with those facilities

what is happening is that this

Format([Reports]![MyReport]![Month Number], "mmmm"))

isnt doing what you think it is.

the date in access counts elapsed days from 30/11/1899
so date value 0 is actually 30/11/1899
so date value 1 is 1/12/1900
and date value 2 is 2/12/1900

so your format is thinking that the value [month number] is a date - now given that this is value 1, it is correctly evaluating that the date represented by value 1, repesents a date in December - but it isnt the December you think it is! And furthermore, date value 2 thru 12 , will also return December, as above! - because they are all dates in December 1900

===========
you really need a mechanmism to select a calendar date, and turn that into a year/month in your fiscal year.

 

[Rik_StHelens]

Thanks VBAInet,

Your code works perfectly.

And also thanks for the explanation as to why Access was converting a 1 to December. I had no idea about this, but its handy to know.

I have checked the field by the way, and it is a date field, and not a text field.

 

[vbaInet]

You're welcome. Glad you got it sorted.

gemma-the-husky always gives good advice.

 

[gemma-the-husky]

vba

that monthname was a good one, and new to me

which version of access did that one appear in?

 

[vbaInet]

Dave,

I think 2000 or 2003.

It even returns the abbreviated month name if you set the 2nd argument to True.