Help with an array??

[Stephd8459]

Hi all,
Looking for some help with my access code. Hopefully I can explain this.

I have a DB with some dates tracking contracts - ContractEffectiveDate and ContEndDate and then a dollar amount assigned to this for the year of the contract.

I have worked out a calculation that determines the monthly cost by determining how many months and divding into the total cost but now I need to assign those month cost to the month in the appropriate year.

So an Example ContractEffectiveDate = 06/01/2010 ContEndDate = 05/31/2011

I want to be able to pull a report for Q4 2010 cost so it should only include Oct Nov Dec for this contract.

I was thinking I needed to create an Array and get the months something like this:
ReDim aryM((Month(Forms!NumberOfMonth.ContractEffectiveDate)) To (Month(Forms!NumberOfMonth.ContEndDate)))
But the syntax on that doesn't seem to work.

If anyone has any suggestions or has seen something like this please point me in the right direction.

Thanks Steph

 

[DCrake]

What happens if in your start month and end month there is only a couple of days in each one? Say from 30/10/2010 to 02/08/2012 will you be asigning a full months budget to each month.

May need to think about asigning values prorata on a daily basis.

 

[Stephd8459]

Dcrake,
That is something I need to look at with the users, but the occurance of these dates in the database are pretty few. Most contracts start /end begin or end of month.

I'm really stumped on the code to assign the cost to month and year. I'd think that once I determine the code for that I would likely be able to build on it to get the proratio.

 

[DCrake]

You need the formula

MonthlyPremium = TotalPremium/Number of Month in contract

So
If total contract cost is 1200.00

Contract is 01/01/2011 to 31/12/2011

Months = DateDiff("m",StartDate,EndDate)

Thus Months = 12

So

1200.00/12 = 1000.00

Convert that into a function

Public Function Premium(dtmStart As Date, dtmEnd As Date,TotalCost As Currency) As Currency

Premium = TotalCost / DateDiff("m",dtmStart,dtmEnd)

End Function

Then in a query

MonthlyPremium:Premium([TotalCost],[StartDate],[EndDate])

Remember to use actual field names ones used above are for brevity only.

 

[Stephd8459]

So, my apologize I wasn't clear.
I have the monthly cost... but I need to connect that $$ amount with a month and year assignment, so I was thinking dynamic multi dimensional array.
So the array would use something like the below data to com up with a cost per month in the appropriate year for each contract.

 

[Stephd8459]

and just a note to the above, I maybe approaching this in the wrong way... I just couldn't come up with another option.

 

[DCrake]

Just what is it you want to find out?

 

[Stephd8459]

Let me see if I can explain.

We have an access database that tracks contracts. On one of the forms is the below section. The fields for each month are manually updated, I'm trying to make this automated, and display the monthly cost for that contract in the current year.

The example below is a new contract and it started in May so Jan through April have no cost. But come 2011 the cost for Jan to April should be here... the associated table only has monthly cost in May-Dec. So Jan 1st if a report was run to show contract expense this would be zero.

This data had previously been kept in an excell spreadsheet with no historical data available and all manual updates. Each year end the user would copy the spreadsheet over to a new one and manually fill in the zero boxes, I'm trying to avoid that manual process.
Please feel free to suggest a better resolution, including the building of additional queries or forms to make this as user friendly and helpful as possible.
Thanks

 

[DCrake]

How are you storing the individual monthy payments? in 12 fields or 12 records in a seperate table. Should be the latter.

 

[Stephd8459]

So right now it is 12 fields hence the not really able to manipulate.

The idea of 12 records in a seperate table is the direction I was headed but I haven't determined the exact set up. if it is 12 records, how is eached tied back to the Contract ID and then Month/year?

 

[DCrake]

When you first create the contract with the start and end dates you all add records to a new table with the following

Id = autonumber Primary Key
FKContractID - Foreign key (Primary Key of contract)
Period - First Day of month of each month
Amount - value of contract for that month - be it 0.00 or value

A record would be created for each month in contract. You can then use cross tab queries to get the results you want. Contracts as rows and periods as columns

 

[Stephd8459]

Dcrake, Thank you so much for the help!

So what you are saying about the new table and indivudual records makes sense.
Is there a way for me to automate the creation of each of these indivdual records? That's what I'm trying to get to.

Thanks

 

[DCrake]

At the point of adding a new contract you wouldl have to run code similar to what is below

Dim Rs As DAO.Recordset
Dim Dtm As Date
Set Rs = CurrentDb.Openrecordset(TblPymts)

ContID = Contract Id
Prds = Number of Periods
Amt = Monthly amount
Dtm = First Of Month of Start Date

For x = 1 To Prds
    Rs.AddNew
    Rs("ContID") = ContID
    Rs("Premium") = Amt
    Rs("PymtPeriod") = Dtm
    Rs.Update
    Dtm = DateAdd("m",1,Dtm)
Next
Rs.Close
Set  Rs = Nothing

 

[Stephd8459]

this worked... thank you!!

Now I just have to work it into system.

Appreciate the assistance