1. ## Quarter (A2K)

Good Afternoon,

I need to find an easier way of doing what I'm doing.

Reports are run each month and quarter (this will contain the previous months/quarter data).
I have DateSerial function in my queries for my monthly & quarterly queries.
However, each quarter the queries need to be adjusted (and often it's forgotten).
Can someone please provide me with an example I might go by.
I've search throughtout the forum and haven't had any luck.

Current MTH code:
I don't have to ever change anything, it will always look to LAST MONTH
<pre>Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)</pre>

Current QTR code:
<pre>DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 1) </pre>

--This will get the 1st day of the CURRENT QTR
<pre>DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 4, 0) </pre>

--This will get the Last day of the CURRENT QTR

To retrieve the 1st day of the PREVIOUS QTR and the LAST DAY of the previous quarter, I use:
<pre>Between DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 1)_
and DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 4, 0)</pre>

I just know there is a better approach to this that would avoid the need in making
the adjustments (like the Monthly code). If someone has a suggestion, I would certainly love to hear it.
Thanks,

2. ## Re: Quarter (A2K)

I'm confused - the expressions you mention for the current quarter are *exactly* the same as the ones you mention for the previous quarter. For the previous quarter, use
<code>
Between DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 - 2, 1) _
And DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 0)
</code>
(I subtracted 3 from the month expressions in your code)

3. ## Re: Quarter (A2K)

No, you're not confused, I am.

The following code will return records for the CURRENT QTR (as outlined in my post and what I need to adjust each quarter).
<pre>Between DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 - 2, 1) _
And DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 0)</pre>

NOTE: the BOLD numeric values

Adjusted Code to return the PREVIOUS Quarter records. It's the same code as above, but needs adjusting each quarter.
<pre>Between DateSerial(Year(Date()), Int((Month(Date()) - 1) / 6) * 3 - 2, 1) _
And DateSerial(Year(Date()), Int((Month(Date()) - 1) / 6) * 3 + 1, 0)</pre>

The CURRENT QTR code is placed in the query and is modified each quarter. What I'm hoping to accomplish is "NOT having to modify it each quarter", along the lines of the Monthly code.
<pre>Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)</pre>

The monthly code never needs modifying, it always retrieves the "PREVIOUS" months records. Maybe that's more clear.

Simply said, I guess....I was hoping there was a way to use the qtr code the same as I use the mth code. Once placed in the query, it never needs modifying.

4. ## Re: Quarter (A2K)

I'm even more confused. The code you post for the current quarter is that for the previous quarter. The code you post for the previous quarter makes no sense at all - it returns either the last quarter of the previous year or the first quarter of the current year, but not the previous quarter.

5. ## Re: Quarter (A2K)

OK....let's try it this way:

Let's forget the QTR code I posted entirely.

I'de like to know if there is code something like the following (only for quarter NOT month):
<pre>Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)</pre>

that instead of capturing the previous months records, captures the previous quarters records w/o having to modify the code each quarter.

7. ## Re: Quarter (A2K)

thank you

#### Posting Permissions

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