Geting the Latest Date for the data (Access XP (2002 format))

Topic

New Reply

I want to create a function that returns the latest date from a table (or query) and put it on a report.

On my report I have a textbox whose control source is
=”This report is for the period ending: “& GetLatestDate( )
that always returns #Error.

General Declarations look like this:
Option Compare Database
Public gdteLatestDate as Date

The function looks like this:
Public Function GetLatestDate( ) As Date
‘run the SQL query to get the latest month
gdteLatestDate = “SELECT Max(tblFinalResults.Date) AS MaxOfDate” & _
“From tblFinalResults”
‘assign the value of the query result to variable
GetLatestDate = Format(gdteLatestDate,”mmm yyyy”)
End Function

There is a “type mismatch” problem because instead of passing the value of the sql statement to the variable gdteLatestDate, I am passing the sql statement as a string “SELECT….From….”. How do I pass the results of the sql query to the variable and not the sql string?

As a workaround, I have a subreport linked to a query that returns the latest date after the textbox on the main report. However, I would like to get the function right.

Thanks

Reply | Quote

Replies

Just use :
=”This report is for the period ending: “& Format(DMax(“Date”,”tblFinalResults”),”mmm yyyy”)
BTW It’s a very bad practice to name your fields with reserved words. (Like Date)

Reply | Quote

Thanks,
This works fine and I did changed the field name.
I still would like to know how to pass the results of an SQL statement back via a function. Do I need to create a recordset?

As always, this board is fantastic!

Reply | Quote

Yes, see the answer of Foigel

Reply | Quote

gdteLatestDate = “SELECT Max(tblFinalResults.Date) AS MaxOfDate” & _
“From tblFinalResults
This line of code assigns the select query to your variable. What you want to do is run the query first, and assign the results to gdtelatestdate. You would have to open your database (Set dbs = DBEngine(0)(0)) and then set a recordset to hold the returned values
(Set rst = dbs.OpenRecordset(“SELECT…”)
then set your gdtelatestdate = rst!Date and return that to the report.

Reply | Quote