Show Date of Last Change on Report 2

[wonderwhy]

Hello!

I have a table that includes a field which I named "Updated" (there are other fields, too). The "Updated" field is updated with the current date when any field in the record is changed. I would like to add a text box to a report that will display the most recent "Updated" date for all records in the report.

Any help would be greatly appreciated! I will gladly provide more details if needed.

Thank you,

Julia

 

[jrbarnett]

If the "Updated" field is part of the recordset or select query of the report, all you have to do is put a textbox on the report and set its controlsource to Updated.
You can then apply formatting as appropriate.

If it is not in the recordsource, you can either add it, or set a textbox to use
DLookup ("Update", "Tablename", "Whereclause&quot
and retrieve the data.

You need to change Tablename to the name of the table, and Whereclause to a string that will retrieve the appropriate update value (not needed if there is only one in the table).

John

 

[natatbh]

If I understand what you whould like to do then it's simple. Create a text box on your report and set the control source proprety to the "updated" field of your table. This will show the latest time updated for that record. If this is not what you wanted, give me some more details.

 

[cygne]

I'm assuming that you want the latest date that appears for ALL rows in the table -- only display one date and that is the latest. If so, create a textbox and set its control source to:

=DMax("Updated","YourTableName&quot


An alternative is to add the aggregate function MAX([Updated]) to your RecordSource by using a query.

Hope this helps.

 

[wonderwhy]

Thank you all for your help!

Cygne, I used the DMax function like you suggested - it worked great (I had already tried this without the DMax and I was only getting the first "Updated" value in the query that the report was based on).

This is what I used at the top of my report in a text box:

="Last Updated: " & DMax("Updated","qryMilestoneReport&quot

Thanks, again. Tek-Tips Rules!

 

[wonderwhy]

If you helpful folks are still out there found a glitch in the solution. Here's the problem:

I print the report mentioned above from a form which contains a subform. When I choose to print the report (rptMilestone) which is based on the query "qryMilestoneReport) I use a macro to print only the records based on the records in the current form/subform:

[CommitteeID]=[Forms]![frmCommittees]![CommitteeID]

Now, using the DMax function, when I print the report I get the highest value in the "Updated" field for the entire query, not just the records in current report.

Any further assistance would certainly be appreciated.

Julia

 

[jrbarnett]

Use this slight modification:


DMax("Updated","qryMilestoneReport", "[CommitteeID]=" & [Forms]![frmCommittees]![CommitteeID])

John

 

[wonderwhy]

Thank you. That works perfectly!