Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

add months to year range in vba

Posted on 2011-03-18
8
Medium Priority
?
343 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 52

Accepted Solution

by:
Gustav Brock earned 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 52

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 52

Expert Comment

by:Gustav Brock
ID: 35169670
You are welcome!

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 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