rolling quarterly showing only 4 quarters

Posted on 2005-04-29
Last Modified: 2008-02-20
I'm using this for a quarterly date function and it seems to be working but I'm off a number or two where it says 2005-Jan. Just wanted to make sure that this isn't messing up between year changes. Also how would I make this a rolling quarterly showing only 4 quarters and formated with just numbers (1,2,3,4) and not the confusing Month names?

Quarter: Year([DateReceived]) & "-" & Left(MonthName(Format([DateReceived],"q")*3-2),3)
Question by:chrisamuel
    LVL 11

    Expert Comment

    Maybe you could do something like this:

    Year([DateReceived]) & "Q" & CInt(Format([DateReceived], "q"))

    This would produce: 2004Q1, 2004Q2, 2004Q3, 2004Q4, 2005Q5, etc..

    In this format, it would be easy to group, sort, etc..
    LVL 11

    Expert Comment


    Quarter: Year([DateReceived]) & "-" & Format([DateReceived],"q")

    LVL 119

    Accepted Solution

    you can simply use

    quarter: Format([DateReceived], "yy0q")    ' this will give you  0501 or

    quarter: Format([DateReceived], "yyyy0q")    ' this will give you  200501 or

    quarter: Format([DateReceived], "yyyy-0q")    ' this will give you  2005-01 or

    quarter: Format([DateReceived], "yyyy-q")    ' this will give you  2005-1 or

    Author Comment

    I actually had a similar idea capricorn with this:
    Quarter: Format([DateReceived],"yyyy" & "-" & "q") but still am wondering if the year changes have anything to do with the numbers being off by one between quarter 4 2004 and quarter 1 2005.  I know that it's a date/time field so that might have something to do with it.  Also I put this in the where clause for DateReceived and am wondering if this is causing any problems.

    Either way, I'll just do a manual check to see what's going on, I guess.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now