Find Latest Date before Current Date

[sharonbl]

I have a table that looks like this :

Date Type Amt
07/11/00 f 100
07/11/00 c 200
20/11/00 f 200
21/11/00 c 250
07/12/00 c 350
09/12/00 c 480
10/12/00 f 300

I’ll be adding a record with Date : 11/12/00, Type : c and Amt : 100 from a form. However, I need to add up the Amt with the record Date : 09/12/00, Type : c and Amt : 480 to make the Amt for Date :11/12/00 to be 580. What I need to do is to always find the latest date followed by the new date I entered. It can be any number of days earlier than the new date. How do I do it?

 

[simongallop]

I am presuming that you are looking for the most recent occurence of a "Type". Presuming that your form has two textboxes and a button then for the OnClick event of the button I would use the following code (where txtType and txtAmt and the 2 textboxes and the table is called MyTable)

dim MyDB as database
dim MyRS as recordset
Dim MySearch as String
Dim PreviousAmt As Single

Set MyDB = currentDB
Set MyRS = MyDB.openrecordset("MyTable",dbopendynaset)

MySearch = "[Type] = '" & Me.txtType & "' "
MyRS.FindLast MySearch
PreviousAmt = MyRS("Amt")

MyRS.AddNew
MyRS("Date") = Now
MyRS("Type") = Me.txtType
MyRS("Amt") = PreviousAmt + val(Me.txtAmt)
MyRS.Update

MyRS.Close
MyDB.Close