Dlookup Help

[SMC]

I want to add a second criteria inside my criteria inside my Dlookup code.

The first criteria is an item number which is input into my form.
The second criteria is the date. I need the most recent date, but not a date more recent than the record I am working on.

The reason for the second criteria is due to missing a weekly entry and going back to enter it at a later time. I don't want data pulled from the newest date, since technically it didnt exist when the data should have been entered.
Example: Data from 6/21 and 6/25 have already been entered. Forwhat ever reason, data for 6/23 did not get entered. When I got back to enter my 6/23 data I need the highest date less than 6/23 to be specified.


Here is what I have so far. It is also an addition function as I need two values from the previous date added to get a new total in the current date.

NEWTotal = DLookup("[OLDTotal]", "Table", "[Record Date] =" & DMax("[Record Date]", "Table", "[IDNumber] =" & Forms!Input!txtIDNumber)) + _
DLookup("[OLDCurrent]", "Table", "[Record Date] =" & DMax("[Record Date]", "Table", "[IDNumber] =" & Forms!Input!txtIDNumber))
Forms!Input!txtOLDTotal = NEWTotal

In summary:
The idea is to add the old total and the previous days increase to get a new total. But I want the ability to be able to add records in non-sequential order and it will always pull the highest date less than the inputdate for that record.



I hope this makes sense,

Thanks!

[orange]

What is the purpose of your database - in general terms?

What is the purpose of this calculation?
If you are storing calculated fields in a database table, people here will advise you that that is a poor technique.
Do your calculations in queries.

[SMC]

Thanks, orange.

I have reassessed my approach. I guess I was so determined on solving my problem the way I originally imagined, I was not considering other options which would make it more simple. I have developed a caculation in a query to obtain the data I need.
-SMC