Linking separate records in a formula-Access97

[TMI]

Hi! I'm in the process of designing a database to keep track of the amortization of bank loans. We have a form set up with the account information (loan amounts, regular payment amounts, person acquiring the loan and their personal information, etc.) with a subform (the actual amortization table with the date, payment amount, how much goes to principal and interest, and the beginning and ending balances). What I would like to do is have Access calculate how much of the payments go to interest and principal. I need a formula that will calculate the amount of days between two payments. I know to use the DateDiff function, but how would I get the program to take the date from the previous record and the current record (in the subform) and compute the number of days. Is it possible to link two records that way if the dates are coming from the same field in the subform, just from different records? I have figured out the rest of the basic formula to compute the amounts of principal and interest...excluding this snag that I've hit. If anyone can help, I'd really appreciate it. Also if anyone knows any good books on Access97 & 2000 that provide helpful information, please let me know! Thanks for the help!

 

[Bigpapou]

Hi,

I think that what you need is SQL and recordset. With them working together you can access precice field in any record in any table and play with them. Look those up in the help, For SQL, use one of these words "SELECT", "FROM" or "WHERE" to search in the help it should get you to something like this:
SELECT [table.field]
FROM

WHERE [condition]

To use them together:

[Type] sub()
Dim rst as recordset
Dim SQL as string

SQL = "SELECT... FROM... WHERE..."
set rst = currentDB.openRecordset(SQL,...)
"your code"
rst.close
end sub

For your books, I'm using "Le programmeur Access97" from F. Scott Baker but I'm not sure if they sell this one anymore.
Another good book is "Peter Norton's Guide to Access97 Programming" this is not his first one so he's good.

 

[MichaelRed]

This has been (Recently) discussed in MS Access or VB. do a search on "Previous Records". You will see:

lostgrrl 7/9/01 (posted 7/2) 3 responses
Calculation using current and previous records in a query

It will show an example. (P.S. Give the example poster a "STAR"!)




MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over