Function that turns string month name into month Number (1 Viewer)

catbeasy

Registered User.
Local time
Today, 08:47
Joined
Feb 11, 2009
Messages
140
Wanting to take a string month name and turn it into a valid month date format..

for example: fcn("January") = 1

Also, how do I change the value of the month, e.g. 1, back to January?

for example, fcn(1) = "January"

I know I could do a select case to do this the long way, but thought maybe there was already a built in access function to do this..

Thanks for any assistance..
 

KenHigg

Registered User
Local time
Today, 11:47
Joined
Jun 9, 2004
Messages
13,327
Seems this would be an extremely easy function to write yourself - ?
 

boblarson

Smeghead
Local time
Today, 08:47
Joined
Jan 12, 2001
Messages
32,059
For month number (this is on my website and is courtesy of AWF user raskew):

Code:
'*******************************************

Public Function RtnMonthNum(pstrMonName As String) As Integer
'*******************************************
'Purpose:   Returns month number (1 - 12)
'           when provided a complete or partial
'           month name.
'Coded by:  raskew
'Input:     From debug (immediate) window:
'           1) ? RtnMonthNum("April")
'           2) ? RtnMonthNum("Sep")
'Output: 1) 4
'           2) 9
'*******************************************

 

Dim strHold  As String
Dim strMonth As String
Dim intMonth As Integer

 

   strMonth = "JanFebMarAprMayJunJulAugSepOctNovDec"
   strHold = Left(pstrMonName, 3)
   intMonth = InStr(strMonth, strHold)
   RtnMonthNum = intMonth \ 3 + 1

 

End Function

And to get the month name from the number:
Code:
MonthName(monthNumberHere)
 

catbeasy

Registered User.
Local time
Today, 08:47
Joined
Feb 11, 2009
Messages
140
Seems this would be an extremely easy function to write yourself - ?
Yes, as I mentioned, could use a select case, but am always looking for built in functions to do the job..
 

alktrigger

Aimless Extraordinaire
Local time
Today, 11:47
Joined
Jun 9, 2009
Messages
124
If there was a built in function for this, do you think 2 people would have suggested doing this in a module?
 

boblarson

Smeghead
Local time
Today, 08:47
Joined
Jan 12, 2001
Messages
32,059
If there was a built in function for this, do you think 2 people would have suggested doing this in a module?
Hey alktrigger:

It would be possible that something built-in existed (we don't know it all :) ) and in fact, I didn't know about the MonthName built-in function for a long time. Learned about it here. But the function to return the number based on month name, was something I still hadn't found and Bob Raskew sent me the code that takes care of that (I had created a much longer one based on a Select statement which still is on my website, along with his much shorter and neater code).
 

catbeasy

Registered User.
Local time
Today, 08:47
Joined
Feb 11, 2009
Messages
140
If there was a built in function for this, do you think 2 people would have suggested doing this in a module?
If you would have taken the time to look at the time's of my 2nd response and Bob's, you would have seen that they were posted at the same time (minute wise) and perhaps wondered if I was only responding to the first response and therefore didn't know at the time there were two responses as you presume in your post. This was indeed the case..

Thanks for the presumption, we all know what that does..
 

catbeasy

Registered User.
Local time
Today, 08:47
Joined
Feb 11, 2009
Messages
140
For month number (this is on my website and is courtesy of AWF user raskew):

Code:
'*******************************************
 
Public Function RtnMonthNum(pstrMonName As String) As Integer
'*******************************************
'Purpose:   Returns month number (1 - 12)
'           when provided a complete or partial
'           month name.
'Coded by:  raskew
'Input:     From debug (immediate) window:
'           1) ? RtnMonthNum("April")
'           2) ? RtnMonthNum("Sep")
'Output: 1) 4
'           2) 9
'*******************************************
 
 
 
Dim strHold  As String
Dim strMonth As String
Dim intMonth As Integer
 
 
 
   strMonth = "JanFebMarAprMayJunJulAugSepOctNovDec"
   strHold = Left(pstrMonName, 3)
   intMonth = InStr(strMonth, strHold)
   RtnMonthNum = intMonth \ 3 + 1
 
 
 
End Function

And to get the month name from the number:
Code:
MonthName(monthNumberHere)
Bob, first part very useful, 2nd part, using Access 97 which apparently doesn't have it..

Thanks for the assistance..
 

Users who are viewing this thread

Top Bottom