Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • Last Modified:

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?

0
ouestque
Asked:
ouestque
3 Solutions
 
peter57rCommented:
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)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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
GijimaAstCommented:
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now