Calculate lentgh of quarter (in days)

  • Thread starter Thread starter Henrootje
  • Start date Start date
H

Henrootje

Hello!

tblROSK
============
ROSK_QuarterNumber Integer 'contains a number that clarifies which
quarter is meant
ROSK_QuarterLength Integer 'should contain the length of the quarter
meant in ROSK_QuarterL

I import a file with data, in that importprocess users enter a value
(eg 54) That means that the data imported is from (5) 2005, (4) fourth
quarter
(1-10-2005 <> 31-12-2005)
(eg 61) That means that the data imported is from (6) 2006, (1) first
quarter
(1-1-2006 <> 31-3-2006)

Now I need to calculate the length of the quarter (x = 31-12-2005
-1-10-2005)

Is there a way to automate this so that the value in ROSK_QuarterLength
calculated based on the contents of ROSK_QuarterNumber ??
 
The number of days in each quarter are always going to be the same
unless it is leap year. Create a sub routine that makes that decision.

Quarter 1 is either 90 or 91 depending on whether it is leap year. (if
the full year is evenly divisible by 4 it is a leap year)

Quarter 2 is 91
Quarter 3 is 91
Quarter 4 is 92
 
if it is quarter one then you can use datedif to compute the days
instead of supplying the number of days.
 
Hello "Henrootje".

Henrootje said:
tblROSK
============
ROSK_QuarterNumber Integer 'contains a number that clarifies
which quarter is meant
ROSK_QuarterLength Integer 'should contain the length of the
quarter meant in ROSK_QuarterL

I import a file with data, in that importprocess users enter a value
(eg 54) That means that the data imported is from (5) 2005,
(4) fourth quarter
(1-10-2005 <> 31-12-2005)
(eg 61) That means that the data imported is from (6) 2006,
(1) first quarter
(1-1-2006 <> 31-3-2006)

Now I need to calculate the length of the quarter (x = 31-12-2005
-1-10-2005)

Is there a way to automate this so that the value in
ROSK_QuarterLength calculated based on the contents of
ROSK_QuarterNumber ??
Click to expand...

ROSK_QuarterLength:
DateDiff("d",DateSerial(2000+Left([ROSK_QuarterNumber],1),
1+3*(Right([ROSK_QuarterNumber],1)-1),1),
DateAdd("m",3,DateSerial(2000+Left([ROSK_QuarterNumber],1),
1+3*(Right([ROSK_QuarterNumber],1)-1),1)))
 
Ron2005 said:
The number of days in each quarter are always going to be the same
unless it is leap year. Create a sub routine that makes that decision.

Quarter 1 is either 90 or 91 depending on whether it is leap year. (if
the full year is evenly divisible by 4 it is a leap year)

Quarter 2 is 91
Quarter 3 is 91
Quarter 4 is 92
Click to expand...

Two ways to get boolLeapYear:

-Abs(([Yr] Mod 4=0)-([Yr] Mod 100=0)+([Yr] Mod 400=0))

-Abs(Day(DateSerial([Yr], 3, 0)) = 29)

Note: The -Abs is used so that these expressions will also work when
True = 1. True = -1 versions:

([Yr] Mod 4=0)-([Yr] Mod 100=0)+([Yr] Mod 400=0)

(Day(DateSerial([Yr], 3, 0)) = 29)


BTW: Q34 is JulyDays + AugustDays + SeptemberDays = 31 + 31 + 30 = 92

Q12 = 90 or 91 (i.e., 90 - boolLeapYear)
Q23 = 91
Q34 = 92
Q41 = 92

Noting that Q23, Q34 and Q41 are constant is a nice observation. I see
no errors Wolfgang's solution; it also looks quite handy for an Update
Query. In fact, I ran a query and his solution resulted in the same
results as mine for ROSK_QuarterNumber's 11 through 94. In implementing
your idea, dealing with the leap year becomes a little unwieldy because
the quarter number must be evaluated more than once. Following your
idea while maintaining Wolfgang's handiness I get something like:

ROSK_QuarterLength: IIf(Val(Right(CStr(ROSK_QuarterNumber),1)) = 1,
90+Abs(Day(DateSerial(2000+Left(CStr([ROSK_QuarterNumber]),1), 3, 0)) =
29), IIf(Val(Right(CStr([ROSK_QuarterNumber]),1)) = 2, 91, 92))

It's about the same length as Wolfgang's solution.

James A. Fortune
(e-mail address removed)
 
BTW: Q34 is JulyDays + AugustDays + SeptemberDays = 31 + 31 + 30 = 92
Click to expand...

This gave me another idea for solving this. This solution is for
academic purposes only.

Using an auxiliary table tblI:

tblI
ID AutoNumber
I Long

tblI
I ID
1 1
2 2
3 3
4 4
5 5

Quarter to Starting Month = 3 * (Q - 1) + 1

StartMo = 3 * (Val(Right(CStr(tblROSK.ROSK_QuarterNumber),1)) - 1) + 1
Yr = 2000 + Val(Left(tblROSK.ROSK_QuarterNumber, 1))

The query should look something like:
SELECT (SELECT SUM(Day(DateSerial(Yr, StartMo + A.I, 0))) FROM tblI AS A
WHERE I < 4) AS ROSK_QUARTERLength FROM tblROSK;

Filling in the details:
SELECT (SELECT SUM(Day(DateSerial(2000 +
Val(Left(tblROSK.ROSK_QuarterNumber, 1)), 3 *
(Val(Right(CStr(tblROSK.ROSK_QuarterNumber),1)) - 1) + 1 + A.I, 0)))
FROM tblI AS A WHERE I < 4) AS ROSK_QUARTERLength FROM tblROSK;

I don't see an easy way to use this in an update query but note that it
gives the same results as the other two solutions. This will sum the
total number of days for each of the months contained in the quarter.

James A. Fortune
(e-mail address removed)
 
Back
Top