Simple Date Question

[toleafs]

Hello.
I have a form where a user enters a date using this format: January 2011. I want to put in a validation to ensure that any date entered in this textbox is the full month and year. Any ideas? Any ideas.....

 

[boblarson]

Yeah, my suggestion is to give them a combo with the months and a combo with the years and let them choose one from each and then store each in a different field so you can just bind them to each individual field. You can bring them together as one in a query when you need it.

 

[toleafs]

I had a combo box, but I wanted to switch because the drop down is showing dates from 2009....

 

[Joe4]

I had a combo box, but I wanted to switch because the drop down is showing dates from 2009....

So why not just change the dates in the source for the combo box?

If you don't already have one, you could set up a table that is used as the source for the combo box where you can limit it to just the dates you want.

 

[toleafs]

I have a table with the date source and need them in case users want history. Maybe there is a sorting I can do?

 

[Joe4]

I have a table with the date source and need them in case users want history. Maybe there is a sorting I can do?

Now, I am confused. I thought from your previous post you did NOT want them to able to choose dates from 2009, but now you are saying that you DO want them to, for historical reasons?

So what is the issue?

 

[toleafs]

I had them for historical reasons and now I wanted to change it to input because the drop down box was getting long.

 

[Joe4]

You could add another field to you table for "custom sorting" purposes, so that you could sort all the old dates would sort to the bottom of your combo box. Would that work?

 

[toleafs]

that is what I was thinking...
thanks

 

[boblarson]

I just build a combo and then in the form's On Load event I would call this function to populate the row source of the combo (so it has the latest years (just paste this function into a STANDARD MODULE).

Function FillComboYears(frm As Form, cbo As ComboBox, intNumberOfYears As Integer, Optional intStartYear As Integer)
    Dim i As Integer
    Dim strRowSource As String
    frm.Controls(cbo.Name).RowSourceType = "Value List"
    Do Until i = intNumberOfYears + 1
        If intStartYear = 0 Then
            strRowSource = strRowSource & Year(Date) - i & ";"
        Else
            strRowSource = strRowSource & intStartYear - i & ";"
            End If
            i = i + 1
        Loop
        frm.Controls(cbo.Name).RowSource = strRowSource
    End Function

And then you can call it in the form's Load event:

FillComboYears Me, Me.Combo6, 15

or if you want a specific start year:

FillComboYears Me, Me.Combo6, 15, 2009