Using query to spread monthly costs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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....
 
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
 
Not really sure how to invoke this...is it a module?

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

Thanks...
 
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
(e-mail address removed)
www.pcdatasheet.com
 
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....
 
Back
Top