Number To Month

[zyus]

My field [month] consists of month in number.

I've been using this expression to convert number to month name

MonthName([Month],True)

My problem is i want to display one month after ..say Jan from 12/Dec

Hope my explaination is clear

 

[Jeff Boyce]

Are you saying that you wish to add 1 to the month? You can do that in a
query.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Duane Hookom]

Have you tried to just add 1?
MonthName([Month]+1)

 

[kc-mass]

If you are actually storing the month name and want to reference the next
months name try this:

Function NextMonth(CurrentMonth)
Select Case CurrentMonth
Case "Jan"
NextMonth = "Feb"
Case "Feb"
NextMonth = "Mar"
Case "Mar"
NextMonth = "Apr"
Case "Apr"
NextMonth = "May"
Case "May"
NextMonth = "Jun"
Case "Jun"
NextMonth = "Jul"
Case "Jul"
NextMonth = "Aug"
Case "Aug"
NextMonth = "Sep"
Case "Sep"
NextMonth = "Oct"
Case "Oct"
NextMonth = "Nov"
Case "Nov"
NextMonth = "Dec"
Case "Dec"
NextMonth = "Jan"
End Select
End Function

Sub TestMonth()
Dim answer As String
answer = NextMonth("Apr")
Debug.Print answer
End Sub

You should though store the actual date involved and then pick out the piece
and display of the date when you query it for reports, et al.

Regards

Kevin

 

[zyus]

Tried..but with #error

Have you tried to just add 1?
MonthName([Month]+1)

--
Duane Hookom
Microsoft Access MVP

My field [month] consists of month in number.

I've been using this expression to convert number to month name

MonthName([Month],True)

My problem is i want to display one month after ..say Jan from 12/Dec

Hope my explaination is clear

 

[John W. Vinson]

My field [month] consists of month in number.

I've been using this expression to convert number to month name

MonthName([Month],True)

My problem is i want to display one month after ..say Jan from 12/Dec

Hope my explaination is clear

I'm surprised that Monthname doesn't work; you'ld need to trick it for
December with

MonthName([Month] MOD 12 + 1, True)

If that doesn't work try: Format(DateSerial(2000, [month] + 1, 1), "mmm")

This will convert the month number (an integer from 1 to 12 I presume) to a
Date/Time field containing the first day of the next month (in 2000, or 2001
if the month is 12; the year is arbitrary), and convert that back to a month
name string.

 

[zyus]

Tried in query but still error

SELECT [Tbl-SKS].MONTH, MonthName([month],True) AS Expr1, [expr1]+1 AS Expr2
FROM [Tbl-SKS];

Are you saying that you wish to add 1 to the month? You can do that in a
query.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

My field [month] consists of month in number.

I've been using this expression to convert number to month name

MonthName([Month],True)

My problem is i want to display one month after ..say Jan from 12/Dec

Hope my explaination is clear

.

 

[John W. Vinson]

Tried in query but still error

SELECT [Tbl-SKS].MONTH, MonthName([month],True) AS Expr1, [expr1]+1 AS Expr2
FROM [Tbl-SKS];

Well, you can't reuse Expr1 in a later calculation; and if it worked, Expr1
would be the text string "Jan" and "Jan" + 1 will indeed give an error.

Try

SELECT [Tbl-SKS].[MONTH], MonthName([MONTH] MOD 12 + 1, True) AS NextMonth
FROM [Tbl-SKS];

The MOD function calculates the remainder: 3 MOD 12 is just 3, but 12 MOD 12
is 0; this will let you add 1 to December and get January instead of a bogus
thirteenth month.

 

[zyus]

With this "MonthName([Month] MOD 12 + 1, True)"
It's worked.

Thanks John

My field [month] consists of month in number.

I've been using this expression to convert number to month name

MonthName([Month],True)

My problem is i want to display one month after ..say Jan from 12/Dec

Hope my explaination is clear

I'm surprised that Monthname doesn't work; you'ld need to trick it for
December with

MonthName([Month] MOD 12 + 1, True)

If that doesn't work try: Format(DateSerial(2000, [month] + 1, 1), "mmm")

This will convert the month number (an integer from 1 to 12 I presume) to a
Date/Time field containing the first day of the next month (in 2000, or 2001
if the month is 12; the year is arbitrary), and convert that back to a month
name string.