Form (Converting month to a value)

[darth sidious]

Hi

I have a txt box on a form in which I type the month of the year e.g January, February etc. What I am having problems with is in another field I need to convert this month to it's corresponding value e.g. January will be 1, February will be 2 and so on.

I have hit a brick wall, please help.

Darth

 

[pr2-eugin]

Try

? Month(DateValue("1-January-2000"))
 1 
? Month(DateValue("1-Dec-2000"))
  12

PS: No offense, but a simple Google search took me to the page for the above code..

 

[darth sidious]

I have tried that, but it is only the month that exists in the text field. It is not a complete date.

January
February
March
...

Which ever month is displayed in the field needs converting to the corresponding value in another field.

 

[CJ_London]

since the number of months is limited to twelve, why not have a combobox with a value list 1;January;2;February;3;March et, set columns to 2 and column widths to 0;2; and limit to list to yes

This has the benefit of typeahead/intellisense (tho' you may have to sort into alphabetical order) and eliminates the possibility of typos

 

[pr2-eugin]

Okay lets say the text box name is "txtMonthName", then the control for getting the month number is called "txtMonthNumber", so you will have to code it as..

Me.txtMonthNumber = [URL="http://www.techonthenet.com/access/functions/date/month.php"]Month[/URL](DateValue("1-" & Me.txtMonthName & "-2013"))

The DateValue function converts the String Date into Date, so the above DateValue("1-" & Me.txtMonthName & "-2013") will give date as, 12/01/2013.. Which would be a proper date.. From which we will get the month number by using Month() function..

PS: You can also use CDate(), instead of DateValue..

EDIT: CJ's solution is what I would go for.. But if the requirement is such, you can use the method I have placed..
http://www.techonthenet.com/access/functions/datatype/cdate.php

 

[darth sidious]

Hi

I have added an image file to enhance the clarity of what I am trying to achieve. Please have a look and get back to me.

Thanks

Darth

 

[pr2-eugin]

So what is the RowSource of the Unbound ComboBox??

 

[darth sidious]

Row source is a list of:

January
February
March
April
May
June
July
August
September
October
November
December

 

[pr2-eugin]

So just change these..
RowSource = 1;"January";2;"February";3;"March";4;"April";5;"May";6;"June";7;"July";8;"August";9;"September";10;"October";11;"November";12;"December"
ColumnCount = 2
ColumnWidth = 0cm;2.5cm
BoundColumn = 1

Then as CJ has pointed, the Numeric equivalent of the Month name will be just Me.ComboBoxName

 

[darth sidious]

The only problem I have with this is that when the data is appended to the tblFitnessTest the fitnessTestID is fine but when month is added it is added as a number rather than "January" for example.

 

[RainLover]

If it helps you will see the months such as Jan, Feb, Mar etc on the form but the numbers 1,2 and 3 will be stored in the table.

 

[darth sidious]

Unfortunately I need the data in the table to be stored as a month. This is the predicament I am in.

 

[pr2-eugin]

Okay the ComboBox has two columns first column is Month Number, second column is Month Name..

Dim mthNum As Integer, mthName As String
[COLOR=Green]'to get month number,[/COLOR]
mthNum = Me.comboBoxName.Column(0)
[COLOR=Green]'to get month name,[/COLOR]
mthName = Me.comboBoxName.Column(1)

 

[CJ_London]

month is added it is added as a number rather than "January"

Not sure how your system is working but you can refer to the second column of you combo box - whan assigning it to your tblFitnessTest use myComboBox.Column(1) rather than myComboBox (columns start from 0 in VBA)

 

[darth sidious]

Where do I put that? Sorry if I come across as an amateur...I am.

 

[pr2-eugin]

Okay is there any code behind the button? Create a text box, and set its Control source to the Month field in the table.. In the after update event of the combo box put the code I have give, i.e. after changing it according to the design..

 

[darth sidious]

I'm not entirely sure what you mean. You will have to break it down in baby steps for me.

I have the append query running on the button.

 

[pr2-eugin]

Can we see the Append query?

 

[darth sidious]

INSERT INTO tblFitnessTest ( TestID, TestMonth, TestComments )
SELECT [Forms]![frmNewTest]![myFitnessTestID] AS Expr1, [Forms]![frmNewTest]![cboMonth] AS Expr2, [Forms]![frmNewTest]![txtComments] AS Expr3;

 

[darth sidious]

Appen query:

INSERT INTO tblFitnessTest ( TestID, TestMonth, TestComments )
SELECT [Forms]![frmNewTest]![myFitnessTestID] AS Expr1, [Forms]![frmNewTest]![cboMonth] AS Expr2, [Forms]![frmNewTest]![txtComments] AS Expr3;