Solved Query to pull data from three previous months (1 Viewer)

[scoutdog44]

I am trying to figure out how to build a query that will pull data from three consecutive months in the past.

I am working with a table that contains open invoices from multiple customers. This table contains a "ShipDate" column with a date format of 01/01/2022.

I want to setup the query so all I need to do each time I need the data is to refresh the data in the table and run the query that pulls all open invoices from month 2, 3 and 4 months ago no matter what day of the month I run the query.

My current formula I am using (see below) will pull data from the correct months but it does not pull the full month. Running the report on 10/24/2022, the query will only pull invoices going back to 6/24/22 and only through 8/24/22. I want all of June and all of August data to pull. How do I correct my query? Thank you in advance!!

Between DateAdd("m",-4,Date()) And DateAdd("m",-2,Date())

 

[theDBguy]

Hi. Welcome to AWF!

To get all data since a specific date in the past, I might just do something like:

>=DateSerial(Year(Date()), Month(Date())-3, 1)

Hope that helps...

 

[ebs17]

BETWEEN DateSerial(Year(Date()), Month(Date())-4, 1) 
    AND DateSerial(Year(Date()), Month(Date())-1, 0)

 

[plog]

You need to dip your chocolate into DB guy's peanut butter.


>=DateSerial(Year(DateAdd("m", -3, Date())), Month(DateAdd("m", -3, Date())), 1)

You are getting results greater than the first day of the month from 3 months prior today and accounting for any year switch over that may occur.

 

[scoutdog44]

BETWEEN DateSerial(Year(Date()), Month(Date())-4, 1)
    AND DateSerial(Year(Date()), Month(Date())-1, 0)

I am getting a syntax error on this - says I did not enter the keyword AND in the between...and operator. Says the correct syntax is a follows: express [Not] Between value 1 and value 2. Not sure what is wrong. Here is my query:

BETWEEN DateSerial(Year(Date()), Month(Date())-4, 1) AND DateSerial(Year(Date()), Month(Date())-1, 0)

 

[scoutdog44]

You need to dip your chocolate into DB guy's peanut butter.


>=DateSerial(Year(DateAdd("m", -3, Date())), Month(DateAdd("m", -3, Date())), 1)

You are getting results greater than the first day of the month from 3 months prior today and accounting for any year switch over that may occur.

Using this, I was pulling in July thru October invoices so I slightly changed your query to below and that included June but I am still pulling in Sept and Oct transactions. I want to pull in only June, July and August when running the report in October. In November, I will want only July, August and September. I am not sure how to modify it from here.

>=DateSerial(Year(DateAdd("m",-3,Date())),Month(DateAdd("m",-4,Date())),1)

 

[plog]

So you want it to return data from the first day of the month 4 months prior to the last day of the prior month:

>=DateSerial(Year(DateAdd("m",-4,Date())),Month(DateAdd("m",-4,Date())),1) AND <DateSerial(Year(Date()), Month(Date()), 1)

 

[arnelgp]

what i would do is to add a Function that will test if the date falls within Last 1, or 2, or 3 or 4 months:

' arnelgp
'
Public Function IsDateInLastXMonth(ByVal iX As Integer, ByVal dteField As Variant, Optional ByVal IncludeNull As Boolean = False) As Boolean
    Dim dteFirst As Date, dteLast As Date
    If IsNull(dteField) Then
        IsDateInLastXMonth = IncludeNull
        Exit Function
    End If
    dteFirst = DateSerial(Year(Date), Month(Date) - iX, 1)
    dteLast = DateSerial(Year(Date), Month(Date), 0)
    IsDateInLastXMonth = (dteField >= dteFirst And dteField <= dteLast)
End Function

Example:
1. to get shipment for Last month (does not include current month):

Select * From yourTable Where IsDateInLastXMonth(1, [ShipDate], False);

2. to get shipment from last 2 months (does not include current month):

Select * From yourTable Where IsDateInLastXMonth(2, [ShipDate], False);

2. to get shipment from last 4 months (does not include current month):

Select * From yourTable Where IsDateInLastXMonth(4, [ShipDate], False);

 

[ebs17]

build a query

A query is little more than a piece of criteria. A more complete statement than SQL statement. I'm just referring to your approach:

SELECT
   *
FROM
   TableX
WHERE
   ShipDate BETWEEN DateSerial(Year(Date()), Month(Date()) - 4, 1)
      AND
   DateSerial(Year(Date()), Month(Date()) - 1, 0)

The query can also use an existing index on ShipDate. If you carry out calculations on the table field, this is not necessary. Next, a calculation is performed on the table field for each record. In contrast, the criteria are calculated only once. That's a significant difference in terms of effort.

 

[scoutdog44]

So you want it to return data from the first day of the month 4 months prior to the last day of the prior month:

>=DateSerial(Year(DateAdd("m",-4,Date())),Month(DateAdd("m",-4,Date())),1) AND <DateSerial(Year(Date()), Month(Date()), 1)

Getting closer! It is pulling in June, July and August just fine but it is also pulling in September and I want to exclude September for my October report. When I roll into November, I will only want July, August and September. Thoughts?

 

[plog]

>=DateSerial(Year(DateAdd("m",-4,Date())),Month(DateAdd("m",-4,Date())),1) AND <DateSerial(Year(DateAdd("m",-1,Date())),Month(DateAdd("m",-1,Date())),1)

 

[Minty]

Just as an aside to all the efforts here, and this is aimed at the OP - learn how to use the immediate window in the VBA editor.
Prefix a statement with "?" which means Print, then you can experiment with the syntax. e.g.

? DateSerial(Year(Date()), Month(Date()), 1)
01/10/2022

 

[scoutdog44]

>=DateSerial(Year(DateAdd("m",-4,Date())),Month(DateAdd("m",-4,Date())),1) AND <DateSerial(Year(DateAdd("m",-1,Date())),Month(DateAdd("m",-1,Date())),1)

Perfect!!! Thank you so much!