Using query to spread monthly costs

M

Mark

I have a 10 year program that has monthly charges I want to populate the same
amont every month for 120 months. How do I set-up a query to have months as
column titles and populate the monthly charge. I have a start date, end
date, and monthly amount to apply.

Thanks....
 
P

PC Datasheet

Dim I As Integer
Dim MyDate As Date
Dim Rst As DAO.Recordset
Set Rst = Me.RecordsetClone
MyDate = Me!StartDate
Do Until MyDate > EndDate
Rst.Add
Rst!Amount = <$XXX.XX>
Rst!ChargeMonth = MyDate
Rst.Update
MyDate = DateAdd("m",1,MyDate)
Rst.MoveNext
Loop
Rst.Close
Set Rst = Nothing
 
M

Mark

Not really sure how to invoke this...is it a module?

Also, it didn't like the Set Rst statement...bombed out.

Thanks...
 
P

PC Datasheet

It sounds to me like you want to do this one time to populate your database
with ten years of data. So create a temporary new form and set the
recordsource to where your start date, end date, and monthly amount are. Add
textboxes that match the code below to display the data.

Regarding Set Rst ..........
Open to where your code is at. Click on Tools - References. Uncheck
Microsoft ADO. If Microsoft DAO is not there, scroll down and check it.
Close References.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
[email protected]
www.pcdatasheet.com
 
M

Mark

I'm not sure if I'm following the logic.

I was thinking there could be a query written that would:

- Set up the columns (months)
- Analyse the start and end dates
- Populate the amount in the columns where start and end meet the criteria

The end result would be a "table" of results

Example:

Amount Start End Jan-05 Feb-05 Mar-05 Apr-05

$100 Feb-05 Mar-05 $100 $100
$200 Jan-05 Apr-05 $200 $200 $200 $200
$150 Mar-05 Apr-05 $150 $150

so on, and so on....
 
Top