# Calculate number of days

I have a query that calculates Event start and end dates for people with living status changes.  What I need to do is put them into columns showing first quarters for a year, then by year after that.

In the attached screen shot, the left side is already done in a query.  The section in yellow is what I need to calculate.  I was thinking of adding a query that would calculate each clients date range column, for example, for the first client there would be a column called 90Date which would contain  dateadd("m",3,#1/7/2014#), 180Date column, etc. .  The second client would have dateadd('m',3,#10/11/13#) and so on.  Then use these calculated dates to calculate the number of days for each event.  But I have a feeling there is a much easier way, I just don't see it right now.

LVL 7
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
All you need to do is just to carry the earliest FromDate for the client on each of the rows. That way it will be very easy to create the columns in the query, or even as a expressions directly in the report. If you do not carry the FromDate you will have to use a sub-query (SELECT MIN(FromDate) From LiveChanges Where ClientId = a.ClientAd) for every row.
If you could show us your original query we can work something out
Author Commented:
Actually, there are more columns that I didn't do a screen shot of.  There is a column called Date_Opened which is in each row.  So in the first example, the date 1/7/2014 is in the Date_Opened for each record for that client.

So, given that Date_Opened field, what I can't figure out is the formula needed to populate the yellow columns.
Commented:
ok, this is easy. To do so in the query use this columns:
``````SELECT Date_Opened,
.... as FromDate,
.... as ToDate,
Iif(ToDate >DateAdd("m", 3, Date_Opened) , DateDiff("d", ToDate, DateAdd("m", 3, Date_Opened) ), 0) as 3mo,
Iif(ToDate >DateAdd("m", 6, Date_Opened) , DateDiff("d", ToDate, DateAdd("m", 6, Date_Opened) ), 0)  as 6mo,
Iif(ToDate >DateAdd("m", 9, Date_Opened) , DateDiff("d", ToDate, DateAdd("m", 9, Date_Opened) ), 0)  as 9mo,
Iif(ToDate >DateAdd("y", 1, Date_Opened) , DateDiff("d", ToDate, DateAdd("y", 1, Date_Opened) ), 0)  as 1yr,
Iif(ToDate >DateAdd("y", 2, Date_Opened) , DateDiff("d", ToDate, DateAdd("y", 2, Date_Opened) ), 0)  as 2yr,
Iif(ToDate >DateAdd("y", 3, Date_Opened) , DateDiff("d", ToDate, DateAdd("y", 3, Date_Opened) ), 0)  as 3yr
....
From theTable
``````
CIOCommented:
You'll be best off with a custom function as shown below.

Then your query could go like this:

Select
ClientId,
Date_Opened,
FromDate,
ToDate,
DaysOfStatus(Date_Opened, FromDate, ToDate, 0, 3) As [0-3 mo],
DaysOfStatus(Date_Opened, FromDate, ToDate, 3, 3) As [4-6 mo],
DaysOfStatus(Date_Opened, FromDate, ToDate, 6, 3) As [7-9 mo],
DaysOfStatus(Date_Opened, FromDate, ToDate, 9, 3) As [10-12 mo],
DaysOfStatus(Date_Opened, FromDate, ToDate, 12, 12) As [1-2 yr],
DaysOfStatus(Date_Opened, FromDate, ToDate, 24, 12) As [2-3 yr]
From
tblYourTable
``````Public Function DaysOfStatus( _
ByVal datOpen As Date, _
ByVal datFrom As Date, _
ByVal datTo As Date, _
ByVal intMonthFirst As Integer, _
ByVal intMonths As Integer) _
As Variant

Dim datFirst    As Date
Dim datLast     As Date
Dim varDays     As Variant

datLast = DateAdd("m", intMonthFirst + intMonths, datOpen)

If DateDiff("d", datFirst, datFrom) > 0 Then
datFirst = datFrom
End If
If DateDiff("d", datLast, datTo) < 0 Then
datLast = datTo
End If

varDays = DateDiff("d", datFirst, datLast)
If varDays <= 0 Then
varDays = Null
End If

DaysOfStatus = varDays

End Function
``````
/gustav

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Worked perfectly.  I went with Gustav's because the function helped keep the query a little cleaner but it looks like chaau's is basically the same thing. :)

Thank you both for your time.
CIOCommented:
You are welcome!

/gustav
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.