Convert MonthName to Integer 3

[THWatson]

Using Access 2000

I have a list box which names the months from January through December.

I realize that these are just strings and not actual months derived from a date, and the IsDate function returns "False." But I am wondering if there is a way to convert these to Integers.

Tom

 

[TonyJollans]

Hi THWatson,

You could try ..

MonthNo

 = Month("1 " &

MonthName

)

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Blorf]

Assuming your month is called MON, the following should work.

=Month(DateValue(Left([Mon],3) & "-01-2005&quot)

Probably a different way but...

ChaZ

Ascii dumb question, get a dumb Ansi

 

[THWatson]

Tony and Chaz
The problem is that the "January"..."December" names are from a Value List in the list box. So the problem is that the names are not actual Month names but merely strings.

I know that I can convert the various names to Integers using Case Select statements, but I wondered if there was a different way. Probably not, since they are merely strings (the names might just as well be "Apples"..."Oranges"

Tom

 

[Blorf]

Both my suggestion, and Tony's deal with Strings.

If the strings Strings are "January" or "March" or any other month, than both of our suggestions will work, because the convert the string to a serial date, and get the interger of the month of that date.

Unless I don't understand what you are saying.

Chaz

Ascii dumb question, get a dumb Ansi

 

[TonyJollans]

Hi Tom,

Both methods work with strings, provided they are valid month names. Have you tried them?

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[MichaelRed]

I would suggest that you use a Multicolumn List box, setting the second column to the "value" (1 to 12) corresponding to the number and just taking the value of htat second column. While the various suggestions already made will easily do the jub, it seems more straight forward to just add the value to the list box and use it directly.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[THWatson]

Tony and ChaZ
I replied this morning, follow the last posting by Tony, but don't know what happed to the reply. I said that further testing indicated that both constructs work. I had tried them but had somehow managed to imcorrectly reference the list box control.

ChaZ, I'm not clear as to the meaning of & "-01-2005" in your construct. Could you help me understand it, for future reference?

MichaelRed
Thanks for your suggestion. I'm going to have a look at that approach.

Thanks to all of your for help.

Tom

 

[Blorf]

No problem.

By taking the string say "December" and adding "-01-2005", you get "Decembe-01-2005" which is a valid date format for date value function. The function makes the string into a date, allowing the month function to retreive the month from the date.

like this.

Month(DateValue(Left([Mon],3) & "-01-2005&quot)

Regarding the list box on your form, assume that the form is called MyForm, and the listbox is called MonthBox. So it would be like this:

=Month(DateValue(Left(Forms!Myform.MonthBox,3) & "-01-2005&quot)

ChaZ

Ascii dumb question, get a dumb Ansi

 

[THWatson]

ChaZ
Thanks for the explanation. Appreciate it.

It's interesting. All 3 formulae work (yours, Tony's, MichaelRed's). Proving again that in Access there is always more than one way to do things.

Have a great day!
Tom

 

[THWatson]

Tony, ChaZ, MichaelRed
I would like to push the envelope a bit.

Thanks to your help, I have the list box working perfectly. I can select any given month, or All months, and get the data I want, which is Totals on several columns in the query.

Now what I am wondering if this is possible...
Say that I want to turn the list box into a multi-select, select January and August, and get the by-month data for the time period from January through August. I would populate a DateStart text box with the DateSerial function that grabs the first day of the month of the first month selected (in this case January 1), and populate a DateEnd text box with the DateSerial function that grabs the last day of the month of the second month selected (in this case August 31).

I know that I can enter the dates January 1 and August 31 directly into the text boxes, but I am trying to do it programmatically. I have tinkered around with the ItemsSelected property, but can't seem to get it quite right.

A push in the right direction would be appreciated.

Thanks.

Tom

 

[TonyJollans]

Hi Tom,

Not entirely sure what you're after here, but the ItemsSelected Property gives you a Collection of numbers to index into the ItemData. Remembering that the items start from zero, this will show you the multiple selections ..

With Me.ListBoxOfMonthNames
    For i = 0 To .ItemsSelected.Count - 1
        MsgBox .ItemData(.ItemsSelected(i))
    Next
End With

Is that enough of a push?

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[THWatson]

Tony
Using your push I put the code below on the AfterUpdate event of the list box. I also put in 2 text boxes for testing purposes to see what would be entered in them.

Dim i As Integer
With Me.lstMonthSelect
For i = 0 To .ItemsSelected.Count - 1
Next
Me.Text18 = .ItemData(.ItemsSelected.Count = 1)
Me.Text20 = .ItemData(.ItemsSelected.Count = 2)
End With

Now, say that I want to make two selections - January and August. When I make the first click in the multi-select list box, Me.Text 18 shows January, as it should. When I make the second click, Me.Text20 shows August, as it should. But Me.Text18 reverts to "All months", which is an entry I have at the very top of the list.

Obviously, I am doing something wrong.

Tom

 

[THWatson]

Tony
Don't worry about this. I can't get the Multi-Select list box to work properly. Maybe it won't do what I want it to do.

I have worked out another method using 2 separate list boxes.

Tom

 

[TonyJollans]

Hi Tom,

Don't give up!!

My For .. Next loop was just to give you an example. The way you have it, it does nothing.

Next, the values .ItemsSelected.Count = 1 (or = 2) which you have as indexes, evaluate to true or false which, as numbers, equate to 0 and -1. For some reason (which I do not know) -1 as an index returns the collection entry number (+)1. So you are not returning the selected entries at all.

Provided you know you have two items selected (by checking the .Count, for example) you can assign the values by, for example, ...

Me.Text18.Value = Me.lstMonthSelect.Itemdata(Me.lstMonthSelect.ItemsSelected(0))

So, in full, ..

If Me.lstMonthSelect.ItemsSelected.Count = 2 Then
    Me.Text18.Value = Me.lstMonthSelect.Itemdata(Me.lstMonthSelect.ItemsSelected(0))
    Me.Text20.Value = Me.lstMonthSelect.Itemdata(Me.lstMonthSelect.ItemsSelected(1))
End If

This will populate both your listboxes when you select the second item. You could add more logic to do different things depending on how many items were selected.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[THWatson]

Tony
Thanks for persisting! It appears that I was on the right track, just didn't know how to build the rails properly.

Your code works perfectly when two months are selected.

Now all I have to do is figure out how to allow for two additional possibilities...
1. how to print the data for only one month if only one month is selected
2. how to print the data for all months if the user selects "All"

So I'm off now to tinker with the query that reads the two text boxes, and see if I can get the logic figured out to allow for all these possibilities.

Tom

 

[THWatson]

Tony
Well, except for one little thing I've got it.

This is my first foray into Multi-Select list boxes, and being able to do everything with one List Box makes for a whole lot neater form than having 3 list boxes (one for single month select, the other 2 for double month select)

The one little thing is that I have been working for a half hour trying to figure out how to reset the List Box to Null after the report is run. I can reset the two text boxes with no difficulty, but not the list box.

I have tried...
Me.lstMonthSelect = Null
Me.lstMonthSelect.ItemsSelected.Count = 0
and a bunch of other things that yielded either no result at all or a "Cannot set value for this object" message.

Say that I selected January and August and ran the report. When I return to the List Box, nothing is actually selected because the text boxes have been reset to Null, however January and August still show darkened, in other words selected.

If I can get this one last piece, I'm in the clear. Any suggestions?

Thanks again for all your help.

Tom

 

[THWatson]

Tony
After trying everything I could think of, it appears that Requery and Refresh and Repaint do not work with Simple multi-select list boxes. It's apparently the nature of the beast to remain selected until deselected.

I gather that Extended multi-select list boxes respond to Requery etc., but they are not quite as easy for the user in this case, when I only want her to be abe to select a maximum of 2 months.

If you happen to know any want to programmatically "de-select" the items in the list box, please let me know. Otherwise I will live with what I have...which is a whole lot better than where I was yesterday.

Best regards.
Tom

 

[TonyJollans]

Hi Tom,

You can set Me.lstMonthSelect.Selected(Index) = False to deselect an Item. If you know which items are selected (remember the array is zero-based) you can explicitly deselect them; if not you can run through them all with For ndx = 0 to Me.lstMonthSelect.Listcount -1 loop.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[THWatson]

Tony
Works like a charm. Here's my code...
Dim ndx As Integer
For ndx = 0 To Me.lstMonthSelect.ListCount - 1
Me.lstMonthSelect.Selected(ndx) = False
Next

Thanks to your help, the form is now working perfectly.

Tom