Auto populate combo box with dates

[BradC]

I'd like to set up a form to generate a report base on a selection in a combo box.
The combo box needs to auto populate with the last 4 financial years plus current.

Example:
2006/07
2007/08
2008/09
2009/10
2010/11

But I need the report to pick up the full start and end dates like this:

1/7/2006 – 30/6/2007
1/7/2007 – 30/6/2008
1/7/2008 – 30/6/2009
1/7/2009 – 30/6/2010
1/7/2010 – 30/6/2011

Appreciate any help

 

[vbaInet]

If today is 1st September 2011, what would be the current financial year period?

 

[BradC]

It's 2011/12

Or

1st July 2011 to 30th June 2012

 

[BradC]

oops Forgot to include the years - edited above

 

[DCrake]

There is an easy way to do this. If you have a combo with the rowsourcetype set to value list, then on the on load of the form perform a loop to populate the combo using the .AddItem option

Dim fYr As Integer

fYr = Year(DateAdd("yyyy",-1,Date))
fYr = fYr & "/" & Right(fYr,2)+1

Me.ComboBox.AddItem
For x = 1 To 4
  fYr = Left(fYr,4)+x & "/" Right(fYr,2) +x
  Me.ComboBox.AddItem fYr
Next

This will give you the 2006/07 items


Then you need 2 functions to get the start and end dates

Public Function FirstDayOfFinYear(AnyFinYear As String) As Date

        FirstDayOfFinYear = CDate("01/07/" & Left(AnyFinYear,4))

End  Function

Public Function LastDayOfFinYear(AnyFinYear As String) As Date

        LastDayOfFinYear = CDate("30/06/20" & Right(AnyFinYear,2))

End  Function

 

[vbaInet]

I try to avoid AddItem if I can set it in one go:

    Dim i As Integer, fyStart As Date, cRowSource As String
    
    fyStart = "1/7/" & Year(DateAdd("m", -6, Date)) - 5
    
    For i = 1 To 5
        cRowSource = cRowSource & "1/7/" & Year(fyStart) + i & " - " & "31/6/" & Year(fyStart) + (i + 1) & "; "
    Next
    
    Me.[COLOR=Red][B]ComboBox[/B][/COLOR].RowSource = Left(cRowSource, Len(cRowSource) - 2)

The same Load event of the form.

 

[BradC]

I would like to use David's because 2006/07 looks nicer than 1/7/2006 - 30/6/2007, but I get Compile error: Argument not optional

Private Sub Form_Load()
Dim fYr As Integer
fYr = Year(DateAdd("yyyy", -1, Date))
fYr = fYr & "/" & Right(fYr, 2) + 1
Me.SelectYear.AddItem
For x = 1 To 4
  fYr = Left(fYr, 4) + x & "/" & Right(fYr, 2) + x
  Me.SelectYear.AddItem fYr
Next
End Sub

 

[vbaInet]

I thought it was the second set of records you wrote that you wanted. Here:

    Dim i As Integer, fyStart As Integer, cRowSource As String
    
    fyStart = Year(DateAdd("m", -6, Date)) - 5
    
    For i = 1 To 5
        cRowSource = cRowSource & fyStart + i & "/" & Right(fyStart + (i + 1), 2) & "; "
    Next

    Me.ComboBox.RowSource = Left(cRowSource, Len(cRowSource) - 2)

 

[BradC]

Hi
It's actually both I'm after, one for display in the combo box 2009/10 and the others to generate a financial yeary report

 

[DCrake]

Private Sub Form_Load()
Dim fYr As Integer
fYr = Year(DateAdd("yyyy", -1, Date))
fYr = fYr & "/" & Right(fYr, 2) + 1
Me.SelectYear.AddItem [B][COLOR="Red"]fYr[/COLOR][/B]
For x = 1 To 4
  fYr = Left(fYr, 4) + x & "/" & Right(fYr, 2) + x
  Me.SelectYear.AddItem fYr
Next
End Sub

 

[BradC]

Hi David

Still get an error with your code sorry