Solved

add months to year range in vba

Posted on 2011-03-18
8
295 Views
Last Modified: 2012-06-21
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
Comment
Question by:PeterBaileyUk
8 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 35166818
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
 
LVL 44

Expert Comment

by:GRayL
ID: 35166871
Helen, as the filter is a string, I believe the double-double quotes are required.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 35167482
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
 

Author Comment

by:PeterBaileyUk
ID: 35168453
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35168505
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
 

Author Comment

by:PeterBaileyUk
ID: 35168519
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
 

Author Closing Comment

by:PeterBaileyUk
ID: 35168528
excellent thx
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35169670
You are welcome!

/gustav
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now