Hi

I have 2 text boxes one is for Start Date, & other is for End Date.

I have seperated the year into 4 quarters ie 1-3 -> 1 st quarter
3-6 -> 2 nd quarter
6-9 -> 3 rd quarter
9-12 -> 4 th quarter

'The problem is lets say for example if the user gives the following information
start Date : 15 March 2001
End Date : 20 August 2001

I want the result to be displayed as

Total NO of Days : 165 days


1st qtr 15 days
2nd qtr 90 days
3rd qtr 60 days
4th qtr -

I tried a lot but I am not able to get better luck on it .

Any help is appreciated

Thanks in Advance

Farook

Access has a built-in function called DateDiff.

you can use it as :

intTotalDays = DateDiff("d", StartDate, EndDate)

This is the same message I e-mailed to you, I just thought I'd put it here incase someone has an easier way of doing this.

I've included some code bellow that will give you what you want. Open a new module and paste the text
I've indicated below into the module. Click in the Procedure Test (the last one) and press F-5.
This will run the procedure. Then open the debug window (CTRL-G). You will see a listing looking something
like : 90,1
15,2

This means that there are 90 days in the first quarter and 15 days in the second quarter.

I'll explain how the code works.

The Procedure Test calls the procedure GetDays with two parameters a from-date and a to-date. I made
the procedure GetDays recursive, it calls itself.

Inside GetDays:

The first thing that is done in GetDays is determine which quarter the from-date belongs to and which
quarter the to-date belongs to.
So from your example, From Date - 01 Jan 2001, To Date - 15 Apr 2001, the value of
intStartQtr would be 1 and the value of intEndQtr would be 4.

If both dates belong to the same quarter then we simply use the DateDiff function to determine
how many days. I have test to make sure the dates are both in the same year. I do not know if your
ranges might span a year but it's better to be safe.

Otherwise, well have to do some processing.
The range spans quarters or years. You had a from-date of 01/01/2001. Remember before we had
determined what quarter the startdate was from, intStartQtr. We take this along with the from-date
and we determine the date of the last day in the quarter intStartQtr. We use the from date to determine
what year we are dealing with.
So we've got the last day of the quarter. Now we print out how many days from your from-date to the
last day in the quarter.
But we still have another quarter. So we'll have to call GetDays again. But before we do that we determine the
date of the first day in the next quarter. We will use that as our start date. We already checked the days
From-date to the end of it's quarter so we can forget about that. So basically we have cut down the
initial range from 01/01/2001..04/15/2001 to 04/15/2001..04/15/2001. That's why I decided to use recursion.
Simplyfy the task by breaking it down into smaller tasks.

So we call GetDays again with a new range. This time when we reach the if statement the values of
intStartQtr and intEndQtr will be the same, 4. and we will print the days but not call the procedure GetDays again.
That's how we get out of calling the GetDays procedure indefinitly.

The other functions are merely helper functions I wrote to determine various things.

To use this I would suggest (more coding) creating a temporay table and store the number of days, intNumberOfDays
variable, along with the quarter, intStartQtr variable, as one record. Then you can use it however you see fit, a report maybe. Then you delete the table once you're done with it. I normally would not advise creating a temporary table, but in
this situation because of the recursion we cannot use a dynamic array.

Hope you understand what's going on and how to use it. If you don't understand anthing i'll try to clarify
for you.

-------------COPY FROM NEXT LINE-------------------------------------------------------------------------

Option Compare Database
Option Explicit

Dim dteStartDate As Date
Dim dteEndDate As Date

Private Function QuarterStartDate(dteVar As Date, intOldQtr As Integer, intNewQtr As Integer) As Date
If intOldQtr = 4 Then
QuarterStartDate = DateSerial(Year(dteVar) + 1, 1 + (3 * (intNewQtr - 1)), 1)
Else
QuarterStartDate = DateSerial(Year(dteVar), 1 + (3 * (intNewQtr - 1)), 1)
End If
End Function

Private Function QuarterEndDate(dteVar As Date, intOldQtr As Integer, intNewQtr As Integer) As Date
If intOldQtr = 4 Then
QuarterEndDate = DateSerial(Year(dteVar) + 1, (3 * intNewQtr), Choose(intNewQtr, 31, 30, 30, 31))
Else
QuarterEndDate = DateSerial(Year(dteVar), (3 * intNewQtr), Choose(intNewQtr, 31, 30, 30, 31))
End If
End Function

Private Function InQuarter(varDate As Date, qtr As Integer) As Boolean
Dim qsDate As Date
Dim qeDate As Date
Dim intYear As Integer
Dim intMonth As Integer
Dim intDay As Integer

qsDate = QuarterStartDate(varDate, 1, qtr)
qeDate = QuarterEndDate(varDate, 1, qtr)
If (varDate >= qsDate) And (varDate <= qeDate) Then
' the start date is within the quarter
InQuarter = True
Else
InQuarter = False
End If
End Function

Private Function Quarter(dteVar As Date) As Integer
Dim idx0 As Integer

idx0 = 1
While (idx0 <= 4)
If InQuarter(dteVar, idx0) Then
Quarter = idx0
idx0 = 5
End If
idx0 = idx0 + 1
Wend
End Function

Private Sub GetDays(sDate As Date, eDate As Date)
Dim intStartQtr As Integer
Dim intEndQtr As Integer
Dim dteQtrEnd As Date
Dim intNumberOfDays as Integer

intStartQtr = Quarter(sDate)
intEndQtr = Quarter(eDate)

dteQtrStart = QuarterStartDate(sDate, 1, intStartQtr)
dteQtrEnd = QuarterEndDate(sDate, 1, intStartQtr)

If (intStartQtr = intEndQtr) And (Year(sDate) = Year(eDate)) Then
intNumberOfDays = DateDiff("d", sDate, eDate) + 1
Debug.Print intNumberOfDays & "," & intStartQtr
Else
intNumberOfDays = DateDiff("d", sDate, dteQtrEnd) + 1
Debug.Print intNumberOfDays & "," & intStartQtr
sDate = QuarterStartDate(sDate, intStartQtr, ((intStartQtr Mod 4) + 1))
GetDays sDate, eDate
End If
End Sub

Private Sub test()
GetDays #1/5/2001#, #4/5/2001#
End Sub

-----------END COPYING IN PREVIOUS LINE------------------------------------------

Good luck,

ntp

[This message has been edited by ntp (edited 01-10-2001).]