number as monthname 2

[flaviooooo]

Hi,

in a form, I have an input of a month, in the form of a number. On th following report, I'd like to display that number as a month-name.

For example:
input : 3

report -> March

I've tried =format(input,"mmmm&quot, but since the input is a number and not a datefield, it keeps giving me January as a result.
Any ideas?

 

[Norris68]

Use the

MonthName(IntegerValue)

function.

 

[flaviooooo]

It doesn't seem to recognize the name MonthName?

Maybe I didn't do it correct, I entered this in the textbox on the report:
MonthName([Forms]![budget]![Month])

I also tried the same code in an 'Report On Open'-event...

 

[Aubs010]

do the following:
[ol]
[li]make sure the Record Source for the report has the column from the table in it[/li]
[li]Create a new unbound text box[/li]
[li]In the Control Source for the Text Box, type:[/li]
[ul]
[li]=MonthName([Numbers])[/li]
[li]where [Numbers] is the column from the table that contains the numbers (1-12)[/li]
[/ul]
[li]Press ENTER[/li]
[li]View the report and see the months written as January/February/March/etc...[/li]
[/ol]

Aubs

 

[PaulBricker]

You would need to write a small function to use the MonthName function.

Function getName(myInput as Integer) as String
getName = MonthName(myInput)
End Function

Put this in a module then in the query that supplies your Report put this in a new column

MyMonth:getName([Month])

Then use the field MyMonth in your Report.

Paul

 

[Aubs010]

it's already there!

If you click on build (...) next to the Control source box, then In the LEFT column, select Functions --> Built-In Functions, then in the MIDDLE column, select Date/Time then in the RIGHT column, select MonthName then hit the HELP button and you will get the following:



MonthName Function
See Also Example Specifics

Description

Returns a string indicating the specified month.

Syntax

MonthName(month[, abbreviate])

The MonthName function syntax has these parts:

Part

Description
month

Required. The numeric designation of the month.

For example, January is 1, February is 2, and so on.
abbreviate Optional. Boolean value that indicates if the month name

is to be abbreviated. If omitted, the default is False, which

means that the month name is not abbreviated.

Aubs

 

[PaulBricker]

Sorry Aubs, I posted my reply at the same time you did but I still can't get the MonthName function to work anyplace except in a Module. I tried using it in a Report and providing it with an integer value to work with and couldn't return anything. I also tried using it in a query and got the same parameter prompt MonthName?

It works fine if you put it in a function and call it from the query.
Maybe they will get your method to work. I couldn't .

Paul

 

[Aubs010]

I just created an unbound text box and had the following code in it:
[ul][li]=MonthName(2)[/li][/ul]
and it returned February

it does it for every month that I change the 2 e.g. 1=January etc...

V.strange it doesn't work for you!!

I'm using Win2k, AccessXP(2002)

Aubs

 

[PaulBricker]

I tried it using a hardcoded integer in an unbound textbox on a Report
=MonthName(2)

and got an error message.

I'm using Win 98/A2000. That's probably the difference.

Paul

 

[CosmoKramer]

In a new module create a function something like this:

Function getmonth(mnth As Integer) As String

Select Case mnth
Case 1
  getmonth = "January"
Case 2
  getmonth = "February"
etc.......
End Select
End Function

In your query add an expression field that will use your integer field with this function:

NameOfMonth: getmonth([input])

will return the month name......

 

[flaviooooo]

Hey,

thanks for the response y'all, but I still can' get it to work.

I'm using Access 97 on a Windows 2000 machine. I tried Paul's solution, but it still gives me the error Sub or function not defined, and points to 'Monthname' in the module.
Do I need to set a reference or something?

 

[PaulBricker]

A quick scan of 97 didn't turn up anything on MonthName so I'm guessing it started evolving in 2000. Take a look at CosmoKramer's post. This is just a function that converts integers to strings and should work fine for you.

Or you can try this directly in the control source for your textbox on the report

=Format([Month] & "/" & 1 & "/" & Year(Date()),"mmmm&quot

Where [Month] is the name of the field that has the integer value. This should return the value you want.

Paul

 

[PaulBricker]

Sorry flaviooooo you will need quotes around the 1 also.
=Format([Month] & "/" & "1" & Year(Date()),"mmmm&quot

Paul

 

[PaulBricker]

Sorry flaviooooo you will need quotes around the 1 also.
=Format([Month] & "/" & "1" & "/" & Year(Date()),"mmmm&quot

Paul

 

[flaviooooo]

Great, thanks Paul.
It works now

Have a star on me

PS Aubs, if my problem would have been in XP, I'm sure your solution would have helped me, so I'm handing you a star as well

 

[Aubs010]

Cheers flaviooooo ;-)

Aubs