Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    82

    Unanswered: User-defined Quarters

    Hi,

    I am trying something similar to what another person wanted awhile ago - ie, work with quarters. Unlike his need (shifting quarter calculations to a fiscal year basis), I wish to define a quarter by the start of a contract date. Let me explain.

    If a contract starts on August 1, I don't wish to have this represented as Quarter 3 (which is what a DatePart function would do), but show this as Quarter 1. In other words, I wish to have each contract record with its own Quarter start, based on the contract start date. Contract A with a start date of October 1, 2003 would be tracked quarterly from that date (Oct 1-Dec 31 would be Q1, Jan 1-Mar 31 would be Q2, etc) and Contract B with a start date of May 1, 2004 would have quarterly time periods encompassing May 1-July 31 (Q1), etc. I tried the IIF function but cannot get it to offset by a varying start date for each contract. Any suggestions? I would like suggestions that I can incorporate into a query as an expression, not a VB program script.
    Thanks,

    Jabo

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Here is one way. Assuming a quarter is 3 months long:

    iif(Date() is between cdate(year(date()) & format(ContractStart,"/mm/dd") and dateadd("m",3,ContractStart),"Q1", iif(Date() is between cdate(year(date()) & format(ContractStart,"/mm/dd") and dateadd("m",6,ContractStart),"Q2", iif(Date() is between cdate(year(date()) & format(ContractStart,"/mm/dd") and dateadd("m",9,ContractStart),"Q3", "Q4")))

    This will work if the contract start is 2001/08/01.

  3. #3
    Join Date
    Mar 2004
    Posts
    82

    User-defined quarters

    Hi,
    I tried modifying the IIF() you suggested with the Access syntax, but cannot seem to get the arguments right. Any thoughts? The correct syntax is of the form: = IIf([OrderAmount] > 1000, "True", "False") and the Between...And is of the form: expression [not] Between...And etc.
    Thanks,
    Jabo



    Originally posted by jmrSudbury
    Here is one way. Assuming a quarter is 3 months long:

    iif(Date() is between cdate(year(date()) & format(ContractStart,"/mm/dd") and dateadd("m",3,ContractStart),"Q1", iif(Date() is between cdate(year(date()) & format(ContractStart,"/mm/dd") and dateadd("m",6,ContractStart),"Q2", iif(Date() is between cdate(year(date()) & format(ContractStart,"/mm/dd") and dateadd("m",9,ContractStart),"Q3", "Q4")))

    This will work if the contract start is 2001/08/01.

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Ya, I messed up the brackets and the "is" should not be there:

    IIf(Date() Between CDate(Year(Date()) & Format([ContractStart],"/mm/dd")) And DateAdd("m",3,[ContractStart]),"Q1", ...

  5. #5
    Join Date
    Mar 2004
    Posts
    82

    User-defined queries

    Hello again,
    Sorry to bother you, but I am a beginner with Access and learning by doing...
    The last correction to the IIF() statement worked - sort of, since it gave out "Q4" for all records, irrespective of the contract Start Date. Hence, I'd like to understand the various components so I can work on it.

    What are the various dates?
    Date(), I imagine, is today's date?
    ContractStart is my [contractstartdate]?
    Cdate?
    DateAdd?

    Basically, I have today's date (or, a date on which a report needs to be printed), and the contract start date. The report would need to use the latter (ie, contract start date) to compile quarters, until the date of report (ie, today's date). What am I missing in my understanding?

    Thanks,
    Jabo

  6. #6
    Join Date
    Mar 2004
    Posts
    82

    User-defined quarters

    Hi,

    The problem seems to be that there is no reset after Q4, so every date past the 12th month from the contract start date gets assigned Q4.

    Short of programming (in VB that I do not know to solve this rightaway), is an expression possible that would allow this? My problem looks like this in tabular format:

    Contract# / ContStart / Product sold / Month sold / Quarter / Rev
    00001 / 04-Apr-2003 / ABC / Sep-03 / ??? / $10K <-- this wd be Q2
    00001 / 04-Apr-2003 / ABC / Dec-03 / ??? / $8K <-- this wd be Q3
    00001 / 04-Apr-2003 / DEF / Nov-03 / ??? / $6K
    00001 / 04-Apr-2003 / ABC / Feb-04 / ??? / $13K

    Each line item shows revenues from a product sale for a particular month. I'd like to be able to give total revenues across all products for each Quarter against a particular contract, interpreted from the start of the contract. Hope this explanation is more helpful.

    Regards,
    Jabo

  7. #7
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I reworked it and am getting the output at the bottom.

    SELECT Table1.ContractStart, IIf(Date() Between CDate(Year(Date()) & Format([ContractStart],"/mm/dd")) And CDate(Year(Date()) & Format(DateAdd("m",3,[ContractStart]),"/mm/dd")),"Q1",(IIf(Date() Between CDate(Year(Date()) & Format([ContractStart],"/mm/dd")) And CDate(Year(Date()) & Format(DateAdd("m",6,[ContractStart]),"/mm/dd")),"Q2",(IIf(Date() Between CDate(Year(Date()) & Format([ContractStart],"/mm/dd")) And CDate(Year(Date()) & Format(DateAdd("m",7,[ContractStart]),"/mm/dd")),"Q3","Q4"))))) AS Expr1
    FROM Table1;

    gives me this output:
    ContractStart Expr1
    9/1/2003 Q2
    5/1/2003 Q4
    2/1/2004 Q1

    According to your last post where the contract start dates are all the same, and it is the month sold that determines the quarter, then the ContractStarts will have to be replaced with monthSold

  8. #8
    Join Date
    Mar 2004
    Posts
    82

    Thanks, jmrSudbury!

    Thanks for taking the trouble to help me out. Much appreciated.
    Regards,
    Jabo

Posting Permissions

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