Don't know where else to post this question...

[Jamescdawson]

Hope I'm describing this correctly...



I am creating a repayment schedule (as a report) and I want to display a series of fields as a column which return (show) a date one month after the date in the field above.

The first repayment date field (Line 1) will show a date one month after the loan was paid out. the Next field below will show the date one month later.

I can't simply insert the "Date + 30" because that would get out of since over the year. If the loan was issued on say the 15th of January, I need the first field to display 15th February and the next would be .... 15th March.... Yes - You've got it!

Now I could do that in Excel, but I don't know how to get Access to do it.

Can anyone suggest ?

Thanks in anticipation

[ItsMe]

Sounds like you might be after the DAtePart() function. A combination of other functions may be what you need too. However, from what I am reading, I would just pass literal text to the date field.

01/15/2015
02/15/2015
etc.

[Jamescdawson]

Hi ItsMe. Thanks for responding. I wasn't familiar with DatePart() I've had a look at it, but I'm afraid I don't know how to use it to get the desired result ! I've had a look at the way DatePart() works and I'm not convinced it lets me increment dates by "One Month"

Maybe you can guide me.... Here's the problem...

I have a report which contains a field called [loanIssuedDate] It sits beside the [LoanAmount] field. My report uses a query to populate the report by selecting a client and their loan account details. I want to deliver a schedule of payments which lists the date each payment is due for the coming 24 months and each of the dates in the list must flow from the [LoanIssuedDate] incrementing by one month. The repayment amount is fixed but the third column is to show a reducing balance calculation. Let's assume that the data in the [LoanIssuedDate] field is 15/1/15. and the [LoanAmount] is £1,000

Below these fields, I have a column of Date fields and a column of Due payments and the 3rd column displaying the calculated Balance. These effectively create a Repayment schedule.

I'm trying to get the first first field in the date column to insert a date one month after the LoanIssuedDate.

I also have to address the problem of the final payment being either slightly less or slightly more than the normal monthly repayments - which I hope to do using an IF function on each repayment field.

What do you reckon?

Jimbo

Sounds like you might be after the DAtePart() function. A combination of other functions may be what you need too. However, from what I am reading, I would just pass literal text to the date field.

01/15/2015
02/15/2015
etc.

[ItsMe]

You are in a catch 22. You should not be using a report to solve a data management issue. Instead, you should be using tables and queries to solve your problem. On the other hand, Report objects are good for doing calculations on summaries of groups. You are going to have to figure out a way to formulate your amortization. It does not sound like you have the tables and queries needed to do this yet. I think you may have the cart ahead of the horse here.

I would not be worried about displaying data at this point in time. Payments are not really associated with a date when they are formulated via amortization. Instead, they are referred to by their payment number. Your formula should determine the number of payments and then you can associate this with the number of months and then worry about dates.

In your case it may be best to enter this data into a table and query the table. So your table will not be dynamic. A formula would not create the table or new records within the table. Just create a record for each payment.

Also, you might want to use text as your date field so there is not any confusion converting the format from the standard used in the U.S. vs. other standards.

Reference
http://allenbrowne.com/ser-36.html

[Jamescdawson]

Right !! Thanks.. I'll digest and reconsider

=Jimbo=