Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Location
    Minnesota
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I知 looking for a way that I can automate my dates. Instead of going in a couple of times a months going into my query and changing the dates to correct my query.

    My typically query outcomes are.
    1. Past 3 months starting at the end of the prior month and go back 3 months.
      Past 6 months starting at the end of the prior month and go back 6 months.
      Past 9 months starting at the end of the prior month and go back 9 months.
      Past 12 months starting at the end of the prior month and go back 12 months.
    2. List Item


    I tried using the formula below, but seem to be pulling by quarters, and if I need to pull a report that shows the past 3 month and I知 half way through the 4th month it doesn稚 let me pull the correct data

    Code:
    3Month: Sum(IIf([Data_Field]>DateAdd("m",-3,Date()),[Amount_Actual_Loan],0))
    
    6Month: Sum(IIf([Data_Field]>DateAdd("m",-6,Date()),[Amount_Actual_Loan],0))
    
    9Month: Sum(IIf([Data_Field]>DateAdd("m",-9,Date()),[Amount_Actual_Loan],0))
    
    12Month: Sum(IIf([Data_Field]>DateAdd("m",-12,Date()),[Amount_Actual_Loan],0))
    Thank you for all you time and help
    TCB

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Corey

    Here's a userdefined function you can place in a module and then call from your query.
    It will always return the 1st of the month for the last n full months and wil adjust to last year if necessary.

    3Month: Sum(IIf([Data_Field]>dteBackDate(3),[Amount_Actual_Loan],0))

    etc.


    Code:
    Public Function dteBackDate(vValue As Variant) As Date
    
      Dim iMonth     As Integer
      Dim iYear      As Integer
      Dim dteCurDate As Date
      
      dteCurDate = Date
      iMonth = Month(dteCurDate) - vValue
      iYear = Year(dteCurDate)
      If iMonth <= 0 Then
        iMonth = 12 + iMonth
        iYear = iYear - 1
      End If
      dteBackDate = DateValue(Format(iMonth, "##") & "/1/" & Format(iYear, "####"))
      
    End Function
    Hope this helps.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by Corey Vlasak View Post
    I知 looking for a way that I can automate my dates. Instead of going in a couple of times a months going into my query and changing the dates to correct my query.

    My typically query outcomes are.
    1. Past 3 months starting at the end of the prior month and go back 3 months.
      Past 6 months starting at the end of the prior month and go back 6 months.
      Past 9 months starting at the end of the prior month and go back 9 months.
      Past 12 months starting at the end of the prior month and go back 12 months.
    2. List Item


    I tried using the formula below, but seem to be pulling by quarters, and if I need to pull a report that shows the past 3 month and I知 half way through the 4th month it doesn稚 let me pull the correct data

    Code:
    3Month: Sum(IIf([Data_Field]>DateAdd("m",-3,Date()),[Amount_Actual_Loan],0))
    
    6Month: Sum(IIf([Data_Field]>DateAdd("m",-6,Date()),[Amount_Actual_Loan],0))
    
    9Month: Sum(IIf([Data_Field]>DateAdd("m",-9,Date()),[Amount_Actual_Loan],0))
    
    12Month: Sum(IIf([Data_Field]>DateAdd("m",-12,Date()),[Amount_Actual_Loan],0))
    Thank you for all you time and help
    TCB
    The last day of the prior month can be calculated from this: Date() - Day(Date())

    the first day of the current month is: Date() - Day(Date()) + 1

    The first day of the prior 3 months can be calculated from this: DateAdd("m",-3, Date() - Day(Date())+1)

    You could turn these into functions to make it easier for you, something like this:
    Code:
    Public Function LastMonthEnd() as Date
       LastMonthEnd = Date() - Day(Date())
    End Function
    
    Public Function Prior3MonthStart() AS Date
       Prior3MonthStart = DateAdd("m",-3, Date() - Day(Date())+1)
    End Function
    In your query, you can now use these functions as follows:

    3Month: Sum(IIF( Data_Field >= Prior3MonthStart() AND Data_Field <= LastMonthEnd(), Amount_Actual_Loan, 0))
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Dec 2010
    Location
    Minnesota
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, I will set it up and let you know how it works for me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •