Re: Report based on calculations of 2 records

Hi Crystal,

The PayFrom and PayTo dates are of dd/MM/yyyy format. If a customer pays
from 01/01/2006 to 10/01/2006, then the PayFrom = 01/01/2006 and PayTo =
10/01/2006 and these values are keyed in to the Subform's respective fields.
If he comes again to pay on a different time for 11/01/2006 to 16/01/2006,
then another transaction record is added.

Because the running number for the transactions are not in sequence due to
the fact that there are many customers' payment history is link to this
Transaction.

The Form and Subform looks something similar to what is shown below:

Form
Name: abc123
ID: MEMBER213
Address: 1 First Street
Phone: 123456789

Subform
-------------------------------------------------------------
Receipt No | Date | PayFrom | PayTo
-------------------------------------------------------------
123 | 01/01/2006 | 01/01/2006 | 10/01/2006
145 | 14/01/2006 | 11/01/2006 | 16/01/2006

The Receipt No is the running number for the Transaction table and the Date
is the date when the customer comes to pay.

The link between the Form and Subform is via the customer's ID, and is not
shown in the Subform since it is hidden.

With this report, I can check whether the PayFrom and PayTo values are keyed
in correctly. If not, PayFrom (for 145) - PayTo (for 123) will not equal to 1.

Thanks for your help.


"strive4peace" wrote:

Hi Kelvin,

Do you have standard PayFrom and PayTo dates? Or do they
vary depending on the customer? How are these dates defined?

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


Kelvin Leong wrote:
I have 2 tables, one is the CustomerInfo and the other their Transactions. I
have a form which shows individual CustomerInfo from the CustomerInfo table,
and a subForm which shows their payment history from the Transactions table
in a data*** format.

From the Transactions table, there are 2 particular columns that is
important, namely PayFrom (date) and PayTo (date). Lets say that this
customer has made 10 payments, records 1 to 10. I want to know whether
(PayFrom (date) from [record 2]) equals to (PayFrom (date) of [Record 1]) +
1. SImilarly, I want to know whether (PayFrom (date) from [record 3]) equals
to (PayFrom (date) of [Record 2]) +1, so on and so forth.

How am I able to generate a report to show that for a particular customers'
payment history is entered correctly, that is the PayFrom (Date) of the
latter record is 1 day after the PayTO (date) of the previous record?

.