Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

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

0
PeterBaileyUk
Asked:
PeterBaileyUk
1 Solution
 
Helen FeddemaCommented:
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.
0
 
GRayLCommented:
Helen, as the filter is a string, I believe the double-double quotes are required.
0
 
Gustav BrockCIOCommented:
You could do something like this:
Private Sub BtnMainFormIntroDateMinus_Click()
Select Case MainFormIntroCount
    Case -9 To -3
        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

Private Sub BtnMainFormIntroDatePlus_Click()

    Dim datestr As String

    Select Case MainFormIntroCount
        Case -6 To -3
            MainFormIntroCount = MainFormIntroCount - 3
            LblIntroMainFormCount.Caption = -MainFormIntroCount / 10
        Case -9
            MainFormIntroCount = 1
            LblIntroMainFormCount.Caption = 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


Then adjust your filtering to use months for negative values of MainFormIntroCount and years for positive values.

/gustav
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
PeterBaileyUkAuthor Commented:
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?
0
 
Gustav BrockCIOCommented:
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
0
 
PeterBaileyUkAuthor Commented:
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

0
 
PeterBaileyUkAuthor Commented:
excellent thx
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now