Automate date changes in query

[TCB]

I’m 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’m half way through the 4th month it doesn’t let me pull the correct data

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

[RetiredGeek]

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.

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.

[MarkLiquorman]

I’m 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’m half way through the 4th month it doesn’t let me pull the correct data

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:

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))

[TCB]

Thanks, I will set it up and let you know how it works for me