Hi Crystal,
The system is made in such a way that the 2 main storage tables are as
mentioned in my earlier replies. And the transaction table is structured in a
way to include PayFrom and PayTo dates is to allow users to compare back to
the Agreement Date.
The reason for such calculations is not only to check whether the records
are keyed in correctly, it allows us to monitor his payment history or
pattern. For example, how frequent he comes to pay and how much he is paying
each time.
We have reports that monitors his overdues. We would also like to further
automate the reports generation to include his payment patterns via
calculation between his payment records.
I am afraid changing the structure is quite tedious and this system is being
used on a daily basis.
I'm actually trying the following code (in a module) to help me with my
solution (Reference from Microsoft):
Option Compare Database
Option Explicit
Function PrevRecVal(KeyName As String, KeyValue, FieldNameToGet As String)
'*************************************************************
' FUNCTION: PrevRecVal()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Dim RS As DAO.Recordset
Dim db As DAO.Database
On Error GoTo Err_PrevRecVal
' The default value is zero.
PrevRecVal = 0
Set db = CurrentDb()
' Get the form recordset.
Set RS = db.OpenRecordset("Transaction", dbOpenDynaset)
' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select
' Move to the previous record.
RS.MovePrevious
' Return the result.
PrevRecVal = RS(FieldNameToGet)
RS.Close
Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function
Function NextRecVal(KeyName As String, KeyValue, FieldNameToGet As String)
'*************************************************************
' FUNCTION: NextRecVal()
' PURPOSE: Retrieve a value from a field in the next form
' record.
'**************************************************************
Dim db As DAO.Database
Dim RS As DAO.Recordset
Set db = CurrentDb()
On Error GoTo Err_NextRecVal
' The default value is zero.
NextRecVal = 0
' Get the form recordset.
Set RS = db.OpenRecordset("Transaction", dbOpenDynaset)
' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select
' Move to the next record.
RS.MoveNext
' Return the result.
NextRecVal = RS(FieldNameToGet)
RS.Close
Bye_NextRecVal:
Exit Function
Err_NextRecVal:
Resume Bye_NextRecVal
End Function
I am able to create a report but for the first record for each customer,
points to a different record as illustrated below:
Name: Customer A
CustID: MEM123456
-------------------------------------------------------------------------------------
Receipt No TodaysDate PayFrom PayTo Previous
TodaysDate
-------------------------------------------------------------------------------------
0010001 01/01/2006 01/01/2006 10/01/2006 26/12/2005
0010203 15/01/2006 11/01/2006 14/01/2006 01/01/2006
0010621 20/01/2006 15/01/2006 26/01/2006 15/01/2006
===================================================
Name: Customer B
CustID: MEM123532
-------------------------------------------------------------------------------------
Receipt No TodaysDate PayFrom PayTo Previous
TodaysDate
-------------------------------------------------------------------------------------
0010101 03/01/2006 01/01/2006 09/01/2006 09/11/2005
0010233 16/01/2006 10/01/2006 12/01/2006 03/01/2006
0010521 18/01/2006 13/01/2006 15/01/2006 16/01/2006
==================================================
Is there a way to make "Previous TodaysDate" = "TodaysDate" for first record
for each segment of the customer's statement as the above report lists all of
the customers?
Thanks.
:
Hi Kelvin,
you said, "We have a statement report that lists the
customer and his payment based on
a query that links the two tables together, via the CustID."
When you issue a statement to a customer, that record needs
to be recorded so the customer can pay it. I realize you
are not wanting to store calculated fields, but once a
statement is issued, that field becomes a piece of data.
you should already have the payment due records in your
system. when the customer pays, it should go against one
(or more) of those records.
the point I am trying to get to is that you should not be
keying in the dates when a payment is received 00 they
should be already known.
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Kelvin Leong wrote:
Hi Crystal,
My structure:
*CustomerInfo*
CustID, autonumber
Lastname, text
Firstname, text
CustNum, text (ie: Member123),
Address
Phone
AgreementStart, date
AgreementEnd, date
*Transactions*
TranID, autonumber
CustID, long integer
ReceiptNo
TranDate, date
PayTo, date
PayFrom, date
My apologies for not letting you know that each customer has an agreement
period to fulfill which is governed by the AgreementStart and AgreementEnd.
The agreement period is considered fulfilled if the customer pays until the
AgreementEnd date.
And for each transactions, my user keys in the PayFrom and PayTo dates based
on how much the customer wants to pay, in terms of days. This form is a
