Error in using MonthName() in reports 1

[paramit81]

Hello All,
I have a cross-tab query which gives me monthly data in a tabular format. In the report which I try to generate from this query, I want to display the name of the month from the month number, for which I use MonthName() function. But somehow in the report it prints out #Error values instead of the name. I dont know whether I am using the function in a wrong way or what? Here is what I do:

In the text field where I want the name of the month, I type
= MonthName([field name])

Can anyone please solve my problem????

 

[harvesterlily]

I have never used that particular function, but why not assign your labels values based on the number in the field:

EX:
assuming text
If monthnum = "1" then
lblMonth.caption = "January"
ElseIf monthnum - "2" then
lblMonth.caption = "January"
elseIf etc...
End if

assuming number
If monthnum = 1 then
lblMonth.caption= "January"
ElseIf monthnum - 2 then
lblMonth.caption = "January"
elseIf etc...
End if

maybe not what your looking for, but hope it helps.

 

[paramit81]

Hi harvestlily,
Thnx for the reply, but the month names in my report are values of a calculated field in the query. And also when I used your solution, it showed the numbers and not names.

 

[harvesterlily]

ElseIf monthnum = 2 then
lblMonth.caption = "February"

You can assign values to the text box as well.

 

[harvesterlily]

it sounds like you got it working but instead of having it equal the month name, which you have to assign, you assigned it the value of your field or the number instead.

 

[paramit81]

Hi harvestlily,
I sound like a dumbo, but I still havent got it and wud require a brief description of how and where to put in these IF ststements that u suggested in the report. If u can plz clarify that, that wud be awesome.

 

[harvesterlily]

this code will be used instead of your monthname function

in the code module for the report. i usually put my statements in the section where my info is: group header, page footer, detail, etc...

in this example monthnum would equal the name of field that has value you want to test.

you will need to add a label or textbox. a label will need to have at least an empty string ("") to maintain its presence on the report. The textbox will need to be unbound, meaning that it doesn't have a controlsource, because you will be assigning it a value. I would probably name it lblMonth or txtMonth(lblMonth from the example)

the if statement will need to include all months 1 - 12

once you've got it working, you will make your test field invisible .Visible = False and will place lblMonth or txtMonth in its place.

Does that make things any clearer? I have a habit of either under or over explaining stuff. Let me know how it goes.

 

[harvesterlily]

paramit81

I am assuming that your field is holding a number or string that is a number.

if it is a date you can use the datepart function
=Datepart("mmmm",[fieldname])
replace mmmm w/ m or mm for number
or mmm for month abbreviation

thanks

 

[dhookom]

When you use
= MonthName([field name])
is [Field Name] a date or is it a number 1-12. If it ain't a number 1-12, it ain't gonna work.

If the field is a date field, try
=Format([Field Name],"mmmm")
or
=Format([Field Name],"mmm")
or
= MonthName(Month([Field Name]))
or
= MonthName(DatePart("m",[Field Name]))




Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[paramit81]

Hi Harvestlily,
Thnx for the help. It does work now. Took me a while to get used to the code module but I have it working now.