Solved

add months to year range in vba

Posted on 2011-03-18
8
329 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 50

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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
 
LVL 50

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 50

Expert Comment

by:Gustav Brock
ID: 35169670
You are welcome!

/gustav
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

679 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