Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

add months to year range in vba

I have a small sub for incrementing a global variable to handle the creation of an introdate filter so i can choose the variance i wish.

It works fine in conjunction with two buttons that increment or decrement the range which then gets dateadded to give me the from daterange.

the scale goes 1 to 100 with 101 being a special integer to mean all records

they want to extend the scle so that when it decrements to 1 it then goes to .9 then .6 then .3 which will be the months or increment from .3, .6, .9 then 1 (being the year)

How can i amend the code to handle that? at present i have the 1 global integer.


Private Sub BtnMainFormIntroDateMinus_Click()
Select Case MainFormIntroCount
    
     
    Case 2 To 5
        MainFormIntroCount = MainFormIntroCount - 1
     Case 6 To 15
        MainFormIntroCount = MainFormIntroCount - 5
        
    Case 16 To 25
        MainFormIntroCount = MainFormIntroCount - 10
     Case 26 To 100
        MainFormIntroCount = MainFormIntroCount - 25
        
        
    
    Case 100 To 101
     MainFormIntroCount = MainFormIntroCount - 1
     
     
     
 End Select
      
      LblIntroMainFormCount.Caption = MainFormIntroCount
      
      
'     Forms!cw_client_matching_form.Filter = "([Intro Date]>=dateadd(""Y"",MainFormIntroCount*-1,now()))"
'
'    Me.FilterOn = True
'    Me.Requery
'    RequeryFilterOffForm
      
      
      
      
      
      
      
End Sub

Private Sub BtnMainFormIntroDatePlus_Click()

    Dim datestr As String

    Select Case MainFormIntroCount
        Case 0 To 4
            MainFormIntroCount = MainFormIntroCount + 1
            LblIntroMainFormCount.Caption = MainFormIntroCount
        Case 5 To 14
            MainFormIntroCount = MainFormIntroCount + 5
            LblIntroMainFormCount.Caption = MainFormIntroCount
        Case 15 To 24
            MainFormIntroCount = MainFormIntroCount + 10
            LblIntroMainFormCount.Caption = MainFormIntroCount
        Case 25 To 75
            MainFormIntroCount = MainFormIntroCount + 25
            LblIntroMainFormCount.Caption = MainFormIntroCount
            
        Case 100

            LblIntroMainFormCount.Caption = "All"
            MainFormIntroCount = MainFormIntroCount + 1
    End Select

    If MainFormIntroCount = 101 Then
'        Filter = ""
'        FilterOn = False
    Else
'        datestr = "#" & Format(DateAdd("yyyy", MainFormIntroCount * -1, Date), "mm/dd/yyyy") & "#"
'        Filter = "[Intro Date] >= " & datestr
'        FilterOn = True
    End If

'    Requery
'    RequeryFilterOffForm

End Sub

Open in new window

Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

Without seeing your database, it is hard to say, but perhaps you could offer a choice of either incrementing/decrementing by years, or by months.  Then use different DateAdd expressions, one for years, and the other for months.  Or just use months, multiplying by 12 to get years.

BTW, your DateAdd lines have peculiar syntax.  The interval argument for years is "yyyy" ("y" is day of year).  Just one set of quotes is needed.
Helen, as the filter is a string, I believe the double-double quotes are required.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

Gustaav its almost there but it allows itself to go to zero and from there I cannot increment if i go to .3 it does so can I make it stop at .3 and not decrement to zero?
It is air code ... but I believe this should allow you to move from zero:

        Case 0 To 4
            MainFormIntroCount = MainFormIntroCount + 1
            LblIntroMainFormCount.Caption = MainFormIntroCount

/gustav
this fixed it, its a coincidence but while i was in the park the negative method sprung to mind

Private Sub BtnMainFormIntroDateMinus_Click()
Select Case MainFormIntroCount
    Case -9 To -6
        MainFormIntroCount = MainFormIntroCount + 3
    Case 1
        MainFormIntroCount = -9
    Case 2 To 5
        MainFormIntroCount = MainFormIntroCount - 1
    Case 6 To 15
        MainFormIntroCount = MainFormIntroCount - 5
    Case 16 To 25
        MainFormIntroCount = MainFormIntroCount - 10
    Case 26 To 100
        MainFormIntroCount = MainFormIntroCount - 25
    Case 100 To 101
        MainFormIntroCount = MainFormIntroCount - 1
 End Select
      
      If MainFormIntroCount < 0 Then
        LblIntroMainFormCount.Caption = -MainFormIntroCount / 10
      Else
        LblIntroMainFormCount.Caption = MainFormIntroCount
      End If
      
'     Forms!cw_client_matching_form.Filter = "([Intro  Date] >= dateadd(""Y"", MainFormIntroCount*-1, Now()))"
'
'    Me.FilterOn = True
'    Me.Requery
'    RequeryFilterOffForm
End Sub

Open in new window

excellent thx
You are welcome!

/gustav