Access:Convert Month to a serial number

I have a table(Name: "Months" Columns: "MonthName", "Month_Number")

How do I make it so that whenever a user enters a month in "MonthName" (In the following format "mmm" [i.e. Jan, Feb, Mar]) a corresponding number appears in the "Month_Number" field in the same record.

i.e. If the user enters "Jan" in the "MonthName" field, "1" appears in the "Month_Number" field
i.e. If the user enters "Feb" in the "MonthName" field, "2" appears in the "Month_Number" field
i.e. If the user enters "Dec" in the "MonthName" field, "12" appears in the "Month_Number" field
i.e. If the user enters "Aug" in the "MonthName" field, "8" appears in the "Month_Number" field

Also If I wanted to create the same effect using a subform where the table above ("Months") is the record source. How would I do it?

ouestqueAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
peter57rConnect With a Mentor Commented:
Why store the month name at all?
If you have a table of month names and numbers, then you could use a combo box on your form to allow the user to select the name but the number to be stored.

0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
there is nothing in to update a table field based on what was entered on another field, you have to use an update query to do this.

Update tableX set Month_Number=Month([month] & "-1-07")
0
 
MikeTooleCommented:
For what purpose is the Months table used? There may well be another (simpler?) solution.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
GijimaAstConnect With a Mentor Commented:
If you are doing this on a form you can use the after update method of MonthName

Private Sub MonthName_AfterUpdate()
    Me.Month_Number = Format(CDate(Me.MonthName & " 01, 2007"), "m")
End Sub

As capricorn1 mentioned this can't be done automatically in a table.
0
 
Gustav BrockCIOCommented:
As Mike says, why store it all?
At any time it can be retrieved:

  MonthNumber = Month(DateValue(MonthName & "/1"))

/gustav
0
 
ouestqueAuthor Commented:
Peter got there first and offered a good alternate solution to my problem. Cap solved the problem by stating my original question can not be done, but proposed solution that works, GijimaAst also had a good solution that works on a form. I will divide points. THANKS!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.