Calculate legnth of quarter (in days)

[Henrootje]

First of all, sorry for eventual double posts, Google gives me an error
when posting this but a copy is sent to my mailbox. Still I do not see
my posts on Google Discussions......

Wel, Hello all there!

I have a problem (but then again, who does not?)

TblROSR <- the table
================
ROSR_QuarterNumber numeric 'contains the number that defines the
quarter
ROSR_QuarterLength numeric 'should contain the length in days of
the quarter


During the import of some textfile with data my users have to input a
number that denotes the quarter (ROSR_QuarterNumber)
EG: if they import a file with data from the fourth quarter of 2005
they will enter:
54 (5, being the year 2005, 4 being the fourth quarter)
(1-10-2005 / 31-12-2005)
61 (6, being the year 2006, 1 being the first quarter)
(1-1-2006 / 31-3-2006)

This value is stored in a numeric field in the table TblROSR

Now I need to calculate the length (in calenderdays) of the quarter
using the value in ROSR_QuarterNumber and fill the field
ROSR_QuarterLength with it.

Any suggestions as to how this best can be done?

TIA,

Henro

 

[Arvin Meyer [MVP]]

Since the length of a quarter only changes on leap year, you never need to
save it to a field, you can always calculate it. I'd just write a simple
function to use in queries, forms, or reports.

Function DaysInQtr(WhichYr As Integer, WhichQtr As Integer) As Integer
Select Case WhichQtr
Case 1
Select Case WhichYr
Case 0, 4, 8
DaysInQtr = 91
Case Else
DaysInQtr = 90
End Select
Case 2
DaysInQtr = 91
Case 3, 4
DaysInQtr = 92
Case Else
DaysInQtr = 0
End Select
End Function

Simply parse out the year and quarter and you always have an answer. At
least until the year 2010, when your concatenation breaks down and you need
to redo your whole plan and database anyway.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Ken Sheridan]

Henro:

Add the following function to a standard module:

Public Function DaysInQuarter(intYear As Integer, intQuarter As Integer) As
Integer

Dim intStartMonth As Integer, intEndMonth As Integer
Dim dtmStartDate As Date, dtmEndDate As Date

Select Case intQuarter
Case 1
intStartMonth = 1
Case 2
intStartMonth = 4
Case 3
intStartMonth = 7
Case 4
intStartMonth = 10
End Select

intEndMonth = intStartMonth + 3

dtmStartDate = DateSerial(intYear, intStartMonth, 1)
dtmEndDate = DateSerial(intYear, intEndMonth, 1)

DaysInQuarter = dtmEndDate - dtmStartDate

End Function

To return the number of days in the quarter call it as follows:

DaysInQuarter(Left(ROSR_QuarterNumber,1),Right(ROSR_QuarterNumber,1))

Ken Sheridan
Stafford, England