Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access:Convert Month to a serial number

Posted on 2007-11-27
6
Medium Priority
?
733 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 800 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 800 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 1

Assisted Solution

by:GijimaAst
GijimaAst earned 400 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

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.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

670 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