Count Quarters Between Dates: DateDiff Fails 1

[catrey]

The following applies to Access 2003 on MS XP Pro

On a form, I have a field [CurrIspQtr] that needs to give a count in 3-month periods, between the current date and a begin date which could be any date of the year. That is: the required Result is the number of quarters from date X to today [Date()].

An "ISP Year" is the 1-year period that may start on any date. That is: If the ISP Year starts on Feb. 23, 2011, the ISP year ends on Feb. 22, 2012. So any date from Feb. 23, 2011 to April 22, 2011 in this example would be Qtr 1 of the ISP year. That is, the current quarter between 02/23/11 and 04/22/11 is Qtr 1, or [CurrISPQtr] = 1. April 23 would start Qtr 2, and so on. If today was March 15, 2012 in this example, we would would be in Qtr 5 relative to the ISP year starting on Feb. 23, 2011.

ISPEffecFrom is the start date of the ISP Year. So I have tried:

=DateDiff("q",[ISPEffecFrom],Date())

Where [ISPEffecFrom] is the start date of the ISP Year.

The above code intended to produce the desired result, is the Control Source for a text box named [CurrISPQtr] and labeled 'Current ISP Quarter.'

But the above code often produces erroneous results. It appears "q" in Access only calculates quarters based on begin dates 01/01, 04/01, 07/01, and 12/01.

Thus, relative to today's date of March 24, 2011:

If the start date of the ISP year( [ISPEffecFrom] )was 09/30/11, the calculation correctly returns '2' as the Current ISP Quarter. BUT, if the start date is 10/01/10, the above code gives an INCORRECT result of '1' as the Current ISP Quarter.

Further: Relative to Date() = 03/24/11, if the ISP Year started 12/01/11, the above DateDiff code gives a result of '0' for [CurrISPQtr] when the correct result should be that we are in ISP Qtr '1'.

I tried replacing 'q' with ('m' *3) but ended up with a data type error.

I have racked my brain examining other Date functions and digging around the internet, but have not been able to come up with anything that defeats Access's interpretation of date-quarters. It appears I have to find a way, possibly in a VBA module, of counting raw 3-month periods between dates instead of quarters; then rounding _down_ to the nearest whole Positive integer (so it would never = 0 which is neither positive nor negative): But I have no real training or expertise as to SQL or VBA, only what I've taught myself thru trial and error -- and I'm lost. Help?

C. Reyes

 

[catrey]

CORRECTION: Where I referred to the way Access appears to calculate quarters, I should have given the 4th quarter as starting on 10/01, not 12/01.

C. Reyes

 

[MichaelRed]

DateDiff counts the 'boundaries' crossed for the indicated period crossed, thus a "month" occurs between the last day of one month and the first day of the folowing; a quarter or year may, like wise, can occur over a single day period.

While this 'behavior' is, perhaps, not obvious - I do believe it is well documented. An illustration of this is often shown as:

? datediff("Y", #12/31/2010#, #1/1/2011#)
 1

MichaelRed

 

[TheAceMan1]

How are ya catrey . . .

[blue]DateDiff[/blue] uses a fixed index (1st of the month) for calculation and there's no way to shift the index to maintain quarter crossover for the new starting date. So [blue]DateDiff[/blue] can't be used.

[blue] So any date from Feb. 23, 2011 to April 22, 2011 in this example would be Qtr 1 of the ISP year.[/blue]

If the above is true then we have:
[blue]Feb 23,2011 to Apr 22,2011
Apr 23,2011 to Jun 22,2011
Jun 23,2011 to Aug 22,2011
Aug 23,2011 to Oct 22,2011
Oct 23,2011 to Dec 22,2011[/blue]
Definitely not quarters! It should be:
[blue]Feb 23,2011 to May 22,2011
May 23,2011 to Aug 22,2011
Aug 23,2011 to Nov 22,2011
Nov 23,2011 to Feb 22,2011[/blue]

Since we can't shift the index we'll have to count the quarters ourselves. So in a module in the modules window, copy/paste the following function:

[blue]Public Function QtrCnt(ByVal BegDate As Date) As Long
   Dim Qtrs As Long, EndDate As Date
   
   EndDate = Date
   
   Do While BegDate < EndDate
      BegDate = DateAdd("m", 3, BegDate)
      Qtrs = Qtrs + 1
   Loop
   
   QtrCnt = Qtrs
   
End Function[/blue]

Then change your control equation to:

[blue]=QtrCnt([ISPEffecFrom])[/blue]

All my testing came out good.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[catrey]

Thank you so much for your replies! And AceMan: You've helped me before if I rightly recall, waybackwhen, and I do _very_ much appreciate it!

Your analysis exactly understood the problem at issue! Meanwhile: I'm constantly getting drawn away and assigned to do all-day projects (such as designing forms in Word or Excel) other than the database I was hired for, so was not able to log in Friday and will be swamped most of today before I can plug in the code you suggested, which trust me I am itching to do!! I'll let you know as soon as I do, what happens.

thx & thx -- this problem has really been a pea in my matress!

C. Reyes

 

[catrey]

Before I go -- I couldn't resist trying to read and follow how the code works... I'm trying to wrap my head around how the:

BegDate = DateAdd("m", 3, BegDate)

...isn't self-referential and wouldn't make the database crazy... (how could X = Y+X... unless Y = 0...)

And I'm not quite getting whether or not what's happening is that when the Function's syntax is:

QtrCnt()

... that whatever date goes in the parenthesis becomes BegDate? And if so, due to the:

Do [b]While[/b] BegDate [b]<[/b] EndDate

...will it still work if BegDate ([ISPEffecFrom]) post-dates Date()? As may sometimes happen since the User may quite likely plug in the beginning date of a new ISP year [ISPEffecFrom] on a day before the new ISP year actually begins (that is — if I'm reading correctly EndDate = Date() and BegDate would be [ISPEffecFrom], will this work While BegDate > EndDate).

I'm not saying I want someone to supply answers to all these issues! (Though I have a feeling I may later ask if someone can point me to a good source for guidence on why X = Y+X works when Y isn't zero [in this case Y = 3]) I'm just saying it'll likely be taking me some time to analyze these points, and test what happens when I plug in the code, until I do understand how it works. I do have a couple reference books at least one of which should help me understand the syntax better. And be useful references if I need to add some additional conditional code for when BegDate is not < EndDate (If I'm even at this point correctly interpreting how that part works).

When I was trying to write Subs to resolve the problem I was writing them in the Form module while writing them with a very inadequate understanding of how to set up the first line and whatever may need to go in the () after the sub name — where you added ByVal etc. — and NOT go in there, or only go in the variable definitions — that is, DIM etc. VERY helpful conceptual assist to put the date math in a Public Function! I can totally see now how that would be the natural approach. Also, so far I've barely worked at all with writing loops, and so this is a really useful point of study, thank you.

yikes! gotta go! hasta later,

C. Reyes