Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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)
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!

  4. #4
    New Lounger
    Join Date
    Jul 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Yes, see the answer of Foigel
    Francois

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •