Solved

Access:Convert Month to a serial number

Posted on 2007-11-27
6
732 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
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 51

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 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