Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    126

    Unanswered: Year Select in CBO

    I want a combo box for selecting either the previous year, the current calendar year, or the following year.
    I set the Row Source Type to: Value List; Row Source to: =Year(now)-1; =Year(now); =Year(now)+1; Limit to List = Yes
    Access didn't like this specification. Once I get the combo to display the 3 years, the value is used to select records based on a number field in the record.

    Pete T.
    PGT

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        With Me.Combo_Years ' Change to the actual name of the Combo.
            .RowSourceType = "Value List"
            .RowSource = Year(Now) - 1 & ";" & Year(Now) & ";" & Year(Now) + 1
            .LimitToList = True
        End With
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Aug 2006
    Posts
    126
    Thanks Sinndho
    I did catch that it should be Year(Now()) but that didn't work. I will try the form open VBA but was curious why that didn't take.
    Pete
    PGT

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As usual, there is more than one way to do this:
    Code:
    Private Sub Form_Load()
    
    Dim FirstYear As Date
    Dim intYear As Integer
    
    Me.cboYears.RowSourceType = "Value List"
    
    FirstYear = DateAdd("yyyy", -1, Date)
    
    For intYear = 0 To 2
      Me.cboYears.AddItem Year(FirstYear) + intYear
    Next intYear
    
    End Sub

    This could easily be modified to include any number of years. To change the number of years prior to the current year, adjust FirstYear by changing the number in red

    FirstYear = DateAdd("yyyy", -1, Date)

    to the number of prior years desired.

    To modify the number of years displayed after the current one, adjust the number in red

    For intYear = 0 To 2

    to reflect the total number of years...past, current and future.

    Linq ;0)>
    Last edited by Missinglinq; 07-17-15 at 23:57.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Pete Townsend View Post
    Thanks Sinndho
    I did catch that it should be Year(Now()) but that didn't work. I will try the form open VBA but was curious why that didn't take.
    Pete
    It did not work because the expression:
    Code:
     =Year(now)-1; =Year(now); =Year(now)+1
    cannot be evaluated (it is not syntactically correct in VBA).
    Have a nice day!

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Sinndho View Post
    It did not work because the expression:
    Code:
     =Year(now)-1; =Year(now); =Year(now)+1
    cannot be evaluated (it is not syntactically correct in VBA).
    Works just fine for me, when tested!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •