Date Problem

[Blkblts]

Hello I am using Access 2002 on a WinXp PC. I have the following in a query.

Count_E_Months: IIf([CEYear]=Format(Date(),"yyyy"),DateDiff("m",[Contract Effective Date],DateAdd("m",1,"1 " & Format$(Date(),"mmmm yyyy"))),0)

Count_R_Months: IIf([RYear]>Format(Date(),"yyyy"),Abs(DateDiff("m",[Renewal Date],DateAdd("m",1,"1 " & Format$(Date(),"mmmm yyyy")))),0)

I need to find the number of months a contract was effective in each month. eg. Date in CEYear = 2005 Date in contract Effective Date 10/01/2005. This will give me 3 for Count_E_Months.

I also need to know the number of months a contract will be effective the next year. Eg Date in RYear = 2006 Date in Renewal Date 10/01/2005. This will give Count_R_Months = 10.

These queries work as long as my Date() and the Contract Effective are in the same year. I have some Contract Effective Dates that are 2004 and Renewed in 2005. I can't seem to get that to work. Even if I need another coloumn or a new query. 10/01/2004 = 15 (12 +3) I tried to create a new query and filter only for current year - 1 to get only 2004 data, but I seem to be unsuccessfull with that. I feel like an idiot. I tried format(Date(),"yyyy") -1 Date() - 365 My query returns no result. I would appreciate any guidance.

thanks

 

[gromit]

Hi -

Can I take a step back and ask what years/dates are you interested in? E.g. are you looking from 2004 forward to the present? To 2006? 2007?

The reason I ask is I am wondering whether it might be easier to look at specific months and then aggregate into a total [But depending on where you are going with this, it may or may not make sense].

See attached for a query that shows how I would calculate the number of effective months for a policy. It works off the current date by using the Date() function, but could be altered for other dates.

There is a second query that uses a table of years and reports the number of effective months in each year (this is sort of what I was thinking when I asked my first question above). It uses each year in the table and figures the effective months based on the whole year (regardless of the current date). You could easily sum this and get the totals (for the span of years shown in the table).

Do you need to separate the original effective period from any renewal period? (E.g. a contract that is effective 10/1/05 then renewed 10/1/06 - how should this be reported?)

I hope that some of this can be of help,

- g

 

[Blkblts]

Thanks for your reply.... I hope this explaination helps...

Well what I am really looking for is to place monthly premium into the proper year for a contract. The contract may span multiple years, but will not be longer then 12 months. I need to figure out the number of months the contract ran in a year so I can eventually multiply the monthly premium by that amount.

A contract effective 10/01/05 with a monthly premium of 20.00 and renewal date of 10/01/06 would have a total of $60.00 for 2005 and 180.00 for 2006.

I will need to use this on contract that could have been effective from 03/01/03 - 03/01/04 etc. They need to trend this for something. If they have already entered the contract dates of 05/01/06 - 05/01/07 These would be 0 because we haven't got to these months yet, but eventually we will and the formula would need to recalculate as we move through the years.

K

 

[gromit]

Thanks for the additional info.

 

[gromit]

See attached.

I first tried to get the premium values for all the years listed in the tblYear table. The first step is qryCalcPremimumByYear, which figures out how many months of premiums. I assumed that a premium is due for the effective date of the contract, and that each contract only lasts 12 months. The calculation is a set of nested IIF statements:

NumMonths: IIf([RefYear]>[EffectYear],IIf([refyear]=[effectyear]+1,Month([effectivedate])-1,0),
      IIf([RefYear]=[EffectYear],12-Month([effectivedate])+1,0))

The second part of this stage is qryResultsAllYears, which just summarizes the first query.

To get the premium values up to a certain date, I added another table of months and generate the premium for each month (qryCalcPremiumbyMonth). The basic approach is the same, I just decide whether to include the month or not (0 or 1). I then added a summary query (qryResultsToDate), but this one uses a parameter so that the user can set the cut off date. Note: the "cut off date" really is only significant for year and date. I am not checking the value of the day. You could easily change this to look at Today() rather than a parameter value.

I did not duplicate these approaches for contract renewals, although the approach would be the same. As noted, the comparisons don't evaluate the day of month. If you need this level of detail (or start prorating based on the number of days in the month), I would probably suggest using VBA code and creating a temporary table to hold the results.

Hopefully this gets you closer, if not or you still have questions, please post back.

- g

 

[Blkblts]

Thank you G..

This was very helpful. I analyzed the data and some of the reference years would need to be tweeked, because there is a date in 2005 and it gave the reference year of 2007, but all in all you have given me alot more to work with. Tomorrow I will spend some time with your query. I really appreciate your help.

K