Displaying Metadata in Report Footer

[Coastie]

Good afternoon-

I have several reports that are based on local tables formed from querying an ODBC database. When the download occurs successfully, I record the date/time as the only record in a LOCAL_UPDATE table. Now, however, I would like the footer of the report to display that date/time. This way, once the report is printed, the user can know how up-to date the data acquisition is, not matter when the report was actually run.

When I put the control source for the textbox as:

=[LOCAL_UPDATE]![UPDATE_LAST]

I get a parameter box asking for a value for LOCAL_UPDATE when I run the report. I can't seem to get any of the aggregate functions to work.
I even went so far as to edit the query, so that it now creates a field [DOWNLOAD_DATE], (set to the current date when the query runs) for all records.

I am trying to do something that can't be done? I am afraid users will base their trust of the data on the date the report was printed, which is not entirely accurate.

Thank you for any help you can provide. This is a low priority item, as I am freelancing this project for a buddy.

Matt

[Coastie]

Nevermind...

I figured out how to use the TableDef property and return the date using VB. Sorry for wasting everyone's time.

Matt

[Denburt]

No problem you might consider using the "Dlookup" function it may or may not be quicker. I would try it both ways just to check.

[Coastie]

No problem you might consider using the "Dlookup" function it may or may not be quicker. I would try it both ways just to check.

It was just easier to do this:

Public Function LastDownload() As Date
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("LOCAL_UPDATE")
    rst.MoveFirst
    LastDownload = rst!UPDATE_LAST
End Function
 

Because there is only one download time, and all the downloads are in the form of make-table queries, all the data is downloaded at the same time, so this makes it a mite easier.

Matt

[Denburt]

You need to close that recordset and set the variable to nothing otherwise you will use way more resources than needed and it will cause your database to expand much more than necessary. Error handling is always a pulls just in case your code is interrupted and is unable to update this table.

Public Function LastDownload() As Date
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("LOCAL_UPDATE")
    rst.MoveFirst
    LastDownload = rst!UPDATE_LAST
 rst.Close
set rst=nothing
End Function

I have a similar date type field that lets my users know what date the data was last updated but I simply used the following, again i am not sure which would be quicker and I wouldn't use to many Dlookups but one or two doesn't seem to hinder the performance.

I put the control source for the textbox as:
=DLookUp("UPDATE_LAST","LOCAL_UPDATE")

FYI:
One more thing I wanted to point out I recently had two instances back to back in two different databases where the underscore really screwed me (MS Access 2003). It only happened when I had two in the same field and the databases were fine for a year or so then one day they went belly up (Microsoft update bug maybe) but it was enough for me to back off on using any special characters anymore. I tend to use a Simple uppercase lower case scenario such as: UpdateLast