Solved

Access:Convert Month to a serial number

Posted on 2007-11-27
6
730 Views
Last Modified: 2013-11-29
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
Comment
Question by:ouestque
6 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 200 total points
ID: 20358058
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 200 total points
ID: 20358090
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
 
LVL 27

Expert Comment

by:MikeToole
ID: 20358114
For what purpose is the Months table used? There may well be another (simpler?) solution.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Assisted Solution

by:GijimaAst
GijimaAst earned 100 total points
ID: 20358525
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 20359163
As Mike says, why store it all?
At any time it can be retrieved:

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

/gustav
0
 

Author Comment

by:ouestque
ID: 20404945
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question