Changing values in a report using DMax()

[JGalletta]

I'm currently creating a database to manage pesticide applications for a farm. Each pesticide application has an REI (Re entry interval) that restricts entry into a given field on the farm for a given amount of time. I've easily programmed the database to calculate the Re-Entry date based on the date of application and the REI.

What I would like to do next is change the value of all re-entry dates for each field to reflect the value of the highest or latest re-entry date.

I'm hoping this is a simple calculation using DMax, but my problem arises here:

The re-entry is calculated from two different tables. (say Application Record and Chem Info) using DateAdd as follows.

DateAdd("h",[tblPesticides]![REI],[tblApplicationRecord]![Date of Application]+[tblApplicationRecord]![Time])

Will I be able to use DMax when the Max value being generated is calculated using fields from two different tables like above?

My early attempts at doing this look something like this - so you know where I'm trying to get with this.

DMax((DateAdd("h",[tblPesticides]![REI],[tblApplicationRecord]![Date of Application]+[tblApplicationRecord]![Time])),[Reports]![AppRecord],"Me.Field Number ='" & [tblFieldApps].[Field] & "'")

Keep in mind that this is in a report (named in the domain part of the DMax string.).

Thanks in advance for any help offered on this subject. Feel free to ask for more info, I'll try to respond as soon as possible.

Regards,
John

 

[jdraw]

It might be helpful for those of us reading your post if you would post a jpeg of your tables and relationships.

 

[JGalletta]

farm8.staticflickr.com/7199/6849251393_fa8dbd9256_b.jpg

All tables and relationships are at this url. I cannot post images or links yet since this is only my second post, so just copy and paste into your browser.

Thanks,
John

 

[vbaInet]

If you're going to set all the records in a table to one value then this would indicate a design problem. That field shouldn't be in that table. That value should be stored only once not multiple times.

 

[JGalletta]

The underlying records in the tables are to remain unchanged in order to comply with regulatory laws. The function of this report is to post record of when it is safe to return to a field based on the latest re entry date of all chemicals applied to that field. Whether or not I change all re entry dates on this report is not up to me, but was per my co-worker's request to minimize the possibility that a person viewing the report could accidentally see an earlier re entry date without seeing the latest date. I also cannot simply remove the earlier records from the report because all chemical applications need to be conspicuously posted. Let's please forget the verbiage and just get to the correct procedure for accomplishing the task at hand. How can I set the value of a text box to the latest of the dates generated by the dateadd calculation for a given field on the farm (first piece of code in my first post)?

 

[vbaInet]

DMax() works on fields that exist in a recordset. A recordset could be a query or a table. The DateDiff() function needs to exist in a recordset.

 

[JGalletta]

However, I do not want to completely discount your last post. Perhaps I can just trigger a sub/function to change the re entry date for each field as a property of each field in the field information table based on the properties of the chemicals applied to each field if the re entry date generated by the current application is later than the previously generated re entry date.

 

[vbaInet]

You don't store calculated values.

 

[JGalletta]

Ok, that's what I was thinking - and what led to my desire for hard coding it the way I tried. I'll take a look at how I'm going to work this tomorrow. Thanks for the help.

 

[vbaInet]

So when you're at it tomorrow, simply pull the necessary fields you need into a query, add the DateAdd() calculation and give it a meaningful name. The default is Expr1 so you can change that to something meaningful, then when you call the DMax() function use this new field in the FieldName parameter.

 

[JGalletta]

Worked just as planned. Thanks for your input!

 

[vbaInet]

Good work JGalleta!