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.
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
Helen, as the filter is a string, I believe the double-double quotes are required.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.Capt ion = MainFormIntroCount
/gustav
Case 0 To 4
MainFormIntroCount = MainFormIntroCount + 1
LblIntroMainFormCount.Capt
/gustav
ASKER
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
ASKER
excellent thx
You are welcome!
/gustav
/gustav
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.