Results 1 to 7 of 7

Thread: Quarter (A2K)

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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:
    (but needs adjustment each quarter)
    <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,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quarter (A2K)

    Yes. See my first reply in this thread.

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quarter (A2K)

    thank you
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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