Text to Number Question

[Davepacey]

I have been working on a database that has thousands of records.
One field contains the Month as a text value, i.e "January" rather than the Month Number. My question is. On a Form, I need a hidden textbox that will take the value from this Month Text Field, and return the correct Month Number. i.e. If textbox is July, return 7

It will be easy for some of you, but I am stuck.

Regards,


Dave

 

[bjackson]

try and use a select case
in the forms on current event

dim MyMonth as string


StrMyMonth=left(me![textboxnamethat holds july etc],3)

select case StrMyMonth

case "Jan"
me![othertextboxname]=1
case "Feb"
me![othertextboxname]=2
case "Mar"
me![othertextboxname]=3

etc etc

end select

 

[AncientOne]

You could base your form on a query with a calculated field[MonthNumber].

use the following type of expression:

IIf([MONTH]="January",1,IIf([MONTH]="February",2.................IIf([MONTH]="December",12,0))))))))))))

If this turns out to be too complex to evaluate, you'll have to add a field and populate it via code.

 

[Davepacey]

Hi, and thanks,
This code gives me a compile error "variable not defined" and the line StrMyMonth = highlighted

Help !

 

[Davepacey]

Ancientone, thanks, that does the trick.

Dave

 

[raskew]

Another approach you might consider:

You can create a mmmm/yyyy string using your text month (year is irrelevant, but must be there)
Then, use the datevalue() function to convert your string to an usable date.
Finally, use the month() function to return the month's integer. This method avoids messy nested Iif(),
Switch() or Select Case solutions.
Here's what it would look like in the debug window.

myMon = "December"
myIntMon = month(datevalue(myMon & "/" & year(date())))
? myIntMon
12

HTH, Bob

 

[Davepacey]

Hi Bob, thanks, I will give it a whirl. Though AncientOnes method works ok in my case.
I am always willing to learn new methods, and grateful for any pointers. I am getting to grips with it slowly.

Dave