How do I convert an inout month number into a month name? (1 Viewer)

helen269 · Mar 1, 2012

I have a report based on a query where in the query the user inputs a month from 1 - 12. How do I make the report say the name of the month instead of the inputted number?

The field in the query is constructed like this:

Field Month: Month([tbl_2_CustomerOrder.OrderDate])

Criteria [Input month (1-12)]

I've tried using =Format$([Month],"mmmm") but it always returns "January". What am I doing wrong?

pbaldy · Mar 1, 2012

You already know that any record returned would be in that month, so just use the OrderDate field in the Format() function. By the way, you realize that you'll get multiple year's data with that criteria?

helen269 · Mar 1, 2012

pbaldy said:
You already know that any record returned would be in that month, so just use the OrderDate field in the Format() function. By the way, you realize that you'll get multiple year's data with that criteria?

Thanks Paul.

I've just tried =Format$([OrderDate],"mmmm") but now I don't get anything, not even a #error.

And don't worry about the year, I have an input for the year as my first field in the query, the month is the second.

helen269 · Mar 1, 2012

helen269 said:
Thanks Paul.

I've just tried =Format$([OrderDate],"mmmm") but now I don't get anything, not even a #error.

Oops, mistake! What I actually get is "Input Year" followed by "Input Month" followed by "OrderDate" - because of the square brackets it wants me to input the order date.

pbaldy · Mar 1, 2012

It's not because of the brackets, it's because it doesn't recognize the field name. What is the actual field name?

helen269 · Mar 1, 2012

pbaldy said:
It's not because of the brackets, it's because it doesn't recognize the field name. What is the actual field name?

In the table and the form it's
OrderDate.

In the query it's
Month: Month([tbl_2_CustomerOrder.OrderDate])

pbaldy · Mar 1, 2012

Try with that full reference, even though it doesn't look right:

=Format$([tbl_2_CustomerOrder.OrderDate],"mmmm")

helen269 · Mar 1, 2012

pbaldy said:
Try with that full reference, even though it doesn't look right:

=Format$([tbl_2_CustomerOrder.OrderDate],"mmmm")

It just comes up asking for a third input:

Enter Parameter Value
tbl_2_CustomerOrder.OrderDate

I already have a control box with the Month value in it which displays as a number so I get "Month: 6" for example instead of "Month: June". I'm going to try making a copy of that box and put the formatting stuff around it and see if that works....

helen269 · Mar 1, 2012

helen269 said:
It just comes up asking for a third input:

Enter Parameter Value
tbl_2_CustomerOrder.OrderDate

I already have a control box with the Month value in it which displays as a number so I get "Month: 6" for example instead of "Month: June". I'm going to try making a copy of that box and put the formatting stuff around it and see if that works....

Nope. It still comes up with "January".

I think I'll leave it as it is, it's only for an Access class assignment. The database works and the tutor can take the month as a number and it'll still pass.

pbaldy · Mar 1, 2012

If you want to post the db here, we can figure it out.

helen269 · Mar 1, 2012

pbaldy said:
If you want to post the db here, we can figure it out.

Really? That's very good of you, thanks!

When it opens you'll need the second from last item on the switchboard. There's not much data in it but enter 2011 for the year and either 6, 7 or 8 for the month.

Thanks again!

pbaldy · Mar 1, 2012

Ah, it's because the date field isn't returned by the query, thus unavailable in the report. You can have a textbox like:

=MonthName([Month])

Fair warning, it will error if there's also a textbox with the same name as the field, which is currently the case.

helen269 · Mar 1, 2012

pbaldy said:
Ah, it's because the date field isn't returned by the query, thus unavailable in the report. You can have a textbox like:

=MonthName([Month])

Fair warning, it will error if there's also a textbox with the same name as the field, which is currently the case.

Ok, I deleted the existing Month boxes in the report, both the text and control box, and put in a control box your =MonthName([Month]) and it's giving a #error.

pbaldy · Mar 1, 2012

Here you go.

helen269 · Mar 1, 2012

pbaldy said:
Here you go.

Wow, that's amazing, thank you!

But I put in exactly the same as this. You've even left in the text label box. I want to understand this, what did you do to make it work?

pbaldy · Mar 1, 2012

I had to change the name of the textbox, which you'll see is now "txtMonth".

helen269 · Mar 1, 2012

pbaldy said:
I had to change the name of the textbox, which you'll see is now "txtMonth".

Of course! So when I deleted the original month label and control boxes - after making a copy of the control box - I didn't eliminate the problem as the copy of the control box that I modified the contents of still had the original conflicting name. Damn!

Anyway, thank you very much indeed for sorting that out for me and explaining where I went wrong. I'll know what to look out for in future in a similar situation.

Thank you!

pbaldy · Mar 1, 2012

Happy to help!

How do I convert an inout month number into a month name? (1 Viewer)

Registered User.

Wino Moderator

Registered User.

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Registered User.

Wino Moderator

Registered User.

Attachments

Wino Moderator

Registered User.

Wino Moderator

Attachments

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Similar threads

Users who are viewing this thread