Form/query calculation using field value of a previous record

[Ken Cobler]

Not sure the best way to do this:

I have a form with one subform. The form is based on mortgage loans. The
subform is based on loan extension options (one-to-many, i.e., every time a
loan is extended, a new child record is created with a new maturity date for
that extension record).

I want to include a feature on the subform, whereby someone can enter the
required notice period prior to the start of the extension, i.e., 90 days
prior, and this will yield the 'notice date'. The notice date calculation
requires that the record use the 'maturity date' of the PRIOR record. (The
logic is that the 'new extension' option requires a timely notice some many
days prior to the end of the previous extension.)

What is the best way to obtain the value of the field of the previous record
to use in a calculation with the subsequent record?

I've tried several queries, but they turn up read-only and despite Allen
Browne's list to correct, I don't see how to correct the problem.

Perhaps I need to write the 'extension maturity date' to the parent table as
a variable, and then pull it back into the child record on the subform....
Not sure how to do that, either, but will research more if that is best...

What is the best method to accomplish what I want to do?

Thanks for your help.

 

[Allen Browne]

You could use a DMax() expression to get the maximum date *less than* the
date in the current record.

In code, you could use the RecordsetClone of the form, set its Bookmark to
the current record, and MovePrevious.

If you used a subquery to return the value from the prior record, you would
get a read-only result, so that's probably not useful.

 

[Ken Cobler]

Thanks, Allen, for responding.

I think the DMax is the way to go, but I'm having trouble getting the syntax
correct.

To recap, I have the following tables:

tblLoans
tblExtensions
The connection is via LoanID, one-to-many.

In the subform called frmExtensions, I have a field called MaturityDate.
What I am trying to do is calculate an automatic "Extension Start Date" which
is the day after the previous 'Maturity date' given for the current LoanID.

What I have done is create a txt field called "ExtStartDate". I have
entered code at the form's OnCurrent event, as follows:

ExtStartDate = DMax("MaturityDate", "tblExtensions",
"Date()<[MaturityDate]")

This does not give me a date that is less than my current MaturityDate.
What it gives me is the maximum date for all extension records of all loans,
not just the record for the current LoanID.

As an experiment, I've also done the following instead of above:

ExtStartDate=DMax("maturityDate","tblextensions","LoanID="&Me![LoanID]).
This produces an answer that is the 'maximum' date for the current LoanID.
However, I cannot figure out how to get the max date that is less than my
current MaturityDate for the current LoanID.

BTW, I'm in Access 2002.

Thanks again for your help.

 

[Allen Browne]

To get the maximum date prior to the current record, for the current loan,
you would need to use something like this:

DMax("MaturityDate", "tblExtensions",
"([MaturityDate] < " & Format(Nz([MaturityDate], Date()),
"\#mm\/dd\/yyyy\#") & ") AND (LoanID = " & Nz([LoanID], 0) & ")")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen, for responding.

I think the DMax is the way to go, but I'm having trouble getting the
syntax
correct.

To recap, I have the following tables:

tblLoans
tblExtensions
The connection is via LoanID, one-to-many.

In the subform called frmExtensions, I have a field called MaturityDate.
What I am trying to do is calculate an automatic "Extension Start Date"
which
is the day after the previous 'Maturity date' given for the current
LoanID.

What I have done is create a txt field called "ExtStartDate". I have
entered code at the form's OnCurrent event, as follows:

ExtStartDate = DMax("MaturityDate", "tblExtensions",
"Date()<[MaturityDate]")

This does not give me a date that is less than my current MaturityDate.
What it gives me is the maximum date for all extension records of all
loans,
not just the record for the current LoanID.

As an experiment, I've also done the following instead of above:

ExtStartDate=DMax("maturityDate","tblextensions","LoanID="&Me![LoanID]).
This produces an answer that is the 'maximum' date for the current LoanID.
However, I cannot figure out how to get the max date that is less than my
current MaturityDate for the current LoanID.

BTW, I'm in Access 2002.

Thanks again for your help.

You could use a DMax() expression to get the maximum date *less than* the
date in the current record.

In code, you could use the RecordsetClone of the form, set its Bookmark
to
the current record, and MovePrevious.

If you used a subquery to return the value from the prior record, you
would
get a read-only result, so that's probably not useful.