Number To Month

  • Thread starter Thread starter zyus
  • Start date Start date
Z

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
 
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.
 
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
 
Tried..but with #error

Duane Hookom said:
Have you tried to just add 1?
MonthName([Month]+1)

--
Duane Hookom
Microsoft Access MVP


zyus said:
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
Click to expand...
Click to expand...
 
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
Click to expand...

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.
 
Tried in query but still error

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


Jeff Boyce said:
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.

zyus said:
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
Click to expand...


.
Click to expand...
 
Tried in query but still error

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

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.
 
With this "MonthName([Month] MOD 12 + 1, True)"
It's worked.

Thanks John




John W. Vinson said:
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
Click to expand...

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.
Click to expand...
 
Back
Top