CDate Format Question

[RXX00]

Hi,

I have a table with 12 records in it that are the months of the year. I have put them in as -

01/01/11
02/01/11
ect
ect

I have a combo box that has that table as its row source but I only want the user to be able to select the month. I could have put Jan, Feb, Mar, ect in the table as text fields but I want to run queries on certain dates so I had to keep it date/time.

Does anyone have any advice how I can only display the actual months in the combo box dropdown?

Thanks in advance.

 

[boblarson]

So, are you going to want to have the queries be selected by month and year? If so, you might consider using two combos - one for the month and one for the year. We can give you code which will populate both so you don't even need a table for the dates. Then we can use those two selections along with the DateSerial function to build the criteria for your query/queries.

 

[RXX00]

So, are you going to want to have the queries be selected by month and year? If so, you might consider using two combos - one for the month and one for the year. We can give you code which will populate both so you don't even need a table for the dates. Then we can use those two selections along with the DateSerial function to build the criteria for your query/queries.

Yeah, I will need the month and the year.

My table has, for instance, "01/05/2011" but I only want the user to be able to see "May 2011".

If I have the "01" at the start of each month the users that I work for will be confused (that happens easily ).

I am still learing a I go with Access so I dont want to get to complex (for me) and then not know how to fix/ammend things if and when I am asked.

Edit - This is a fairly simple DB, I have alist box of data that has been stored on a table and I have a combo box with the months of the year. When the user selects a month in the combo box the list box only displays the data aplicable to that month. This is what I am trying to accomplish.

 

[boblarson]

When the user selects a month in the combo box the list box only displays the data aplicable to that month. This is what I am trying to accomplish.

The first thing up is the code which can populate the combo boxes so you don't need a table.

Create a new STANDARD MODULE (not a form, report, or class module but one where you do New Module). And then paste this code into it:

DO NOT CHANGE ANYTHING IN THESE TWO FUNCTIONS - just paste them as is.

Function FillMonthCombo(cbo As ComboBox)
    Dim strMonths As String
    Dim i      As Integer
    For i = 1 To 12
        strMonths = strMonths & i & ";" & Format(DateSerial(Year(Date), i, 1), "mmmm") & ";"
    Next
    With cbo
        .RowSourceType = "Value List"
        .RowSource = strMonths
        .ColumnCount = 2
        .ColumnWidths = "0 in;2 in"
    End With
End Function
Function FillYearCombo(cbo As ComboBox)
    Dim strYears As String
    Dim i As Integer
    Dim y As Integer
 
    y = Year(Date) - 10
 
    For i = Year(Date) To y Step -1
        strYears = strYears & i & ";"
    Next
 
    With cbo
        .RowSourceType = "Value List"
        .RowSource = strYears
        .ColumnCount = 1
        .ColumnWidths = "1 in"
    End With
 
End Function

and save the module with the name of basFillCombos.



Next you open the form in design view and then in the properties dialog you find the EVENTS tab. Find the one labeled ON OPEN and then put your cursor on that line. Select [Event Procedure] from the list and an Ellipsis (...) should appear to the far right of that. Click on that to open up the VBA window. You will then make the function calls to fill your combo boxes (month and year). Name the month combo box cboMonth and the year combo cboYear and then you would have this in the form's On Open event:

Private Sub Form_Open()
   FillMonthCombo Me.cboMonth
   FillYearCombo Me.cboYear
End Sub

And that is it. It will give you the combo lists you will need. The year one I made so it would include this year and 10 years prior. If you need more you can change that in the code which is in that standard module. Just let me know if you need help with that.

So, once you have that all done, let me know and we'll move on to creating the criteria for your query.

 

[RXX00]

The first thing up is the code which can populate the combo boxes so you don't need a table.

Create a new STANDARD MODULE (not a form, report, or class module but one where you do New Module). And then paste this code into it:

DO NOT CHANGE ANYTHING IN THESE TWO FUNCTIONS - just paste them as is.

Function FillMonthCombo(cbo As ComboBox)
    Dim strMonths As String
    Dim i      As Integer
    For i = 1 To 12
        strMonths = strMonths & i & ";" & Format(DateSerial(Year(Date), i, 1), "mmmm") & ";"
    Next
    With cbo
        .RowSourceType = "Value List"
        .RowSource = strMonths
        .ColumnCount = 2
        .ColumnWidths = "0 in;2 in"
    End With
End Function
Function FillYearCombo(cbo As ComboBox)
    Dim strYears As String
    Dim i As Integer
    Dim y As Integer
 
    y = Year(Date) - 10
 
    For i = Year(Date) To y Step -1
        strYears = strYears & i & ";"
    Next
 
    With cbo
        .RowSourceType = "Value List"
        .RowSource = strYears
        .ColumnCount = 1
        .ColumnWidths = "1 in"
    End With
 
End Function

and save the module with the name of basFillCombos.



Next you open the form in design view and then in the properties dialog you find the EVENTS tab. Find the one labeled ON OPEN and then put your cursor on that line. Select [Event Procedure] from the list and an Ellipsis (...) should appear to the far right of that. Click on that to open up the VBA window. You will then make the function calls to fill your combo boxes (month and year). Name the month combo box cboMonth and the year combo cboYear and then you would have this in the form's On Open event:

Private Sub Form_Open()
   FillMonthCombo Me.cboMonth
   FillYearCombo Me.cboYear
End Sub

And that is it. It will give you the combo lists you will need. The year one I made so it would include this year and 10 years prior. If you need more you can change that in the code which is in that standard module. Just let me know if you need help with that.

So, once you have that all done, let me know and we'll move on to creating the criteria for your query.

Thats very much, I will get back to you tomorrow monring when I implament this at work. UK time that is.

Thanks again.

 

[boblarson]

Thats very much, I will get back to you tomorrow monring when I implament this at work. UK time that is.

Just remember it will take me several hours to respond if you post early in the morning because at that time I am sleeping. (Portland, Oregon, USA time )