Results 1 to 10 of 10

Thread: Need last date

  1. #1
    Join Date
    Jul 2009
    Posts
    185

    Question Unanswered: Need last date

    I have a planes table that has list of planes and one field is lastdateflown. How do I find the newest lastdateflown into a variable for Plane.apid=1?
    I can create a sql that shows a list of the Planes but I just want the one plane newest lastdateflown.

    I hope this makes sense?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    The same question was answered here earlier today actually.
    Me.Geek = True

  3. #3
    Join Date
    Jul 2009
    Posts
    185
    I am looking for a dlookup like function that I get just one date the newest lastflowndate that I can load into a variable not just view in a table.

  4. #4
    Join Date
    Jul 2009
    Posts
    185
    I fiqure I need something like this. Still researching how to move last or do a loop till eof doing a move next somehow. Am I at least in the ball park?

    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()

    strSQL = "SELECT Planes.apID, Planes.spDescription, Planes.dLastTime, Planes.dLDate
    FROM Planes
    ORDER BY Planes.dLDate"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    ? move to last somehow
    Debug.Print rs!sDescription
    Debug.Print rs!dlDate
    Debug.Print rs!dLastTime

    Set rs = Nothing
    Set db = Nothing"
    Last edited by Bob2119; 07-15-09 at 20:53.

  5. #5
    Join Date
    Jul 2009
    Posts
    185

    Smile I did it ya for me

    I did it.
    Just have to sort by DESC and I am on newest record. Don't need any kind of move last or move next loop. Though I am still looking for that function.

    Public Function fPlaneLastDate()
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()

    strSQL = "SELECT Planes.apID, Planes.spDescription, Planes.dLastTime," & "Planes.dLDate FROM Planes ORDER BY Planes.dLDate DESC"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    '? move to last somehow sort by desc I am at last.
    Debug.Print "Description =" & rs!spDescription
    Debug.Print rs!dLDate
    Debug.Print rs!dLastTime

    Set rs = Nothing
    Set db = Nothing
    End Function

  6. #6
    Join Date
    Jul 2009
    Posts
    185
    Currently trying the following but the movelast seems to fail with no error generated?
    Public Function fPlaneLastDate()
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()

    strSQL = "SELECT Planes.apID, Planes.spDescription, Planes.dLastTime, Planes.dLDate "
    strSQL = strSQL & "FROM Planes ORDER BY Planes.dLDate DESC"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    rs.MoveLast
    Debug.Print "Description =" & rs!spDescription
    Debug.Print rs!dLDate
    Debug.Print rs!dLastTime

    Set rs = Nothing
    Set db = Nothing
    End Function

  7. #7
    Join Date
    Jul 2009
    Posts
    185
    Dah it did exactly what I told it. I forgot to remove the "DESC" from the strSQL statement.

    I wonder what the difference between using "rs.movelast" or just sort the query or what about rs.findlast(Have not tested that yet)

  8. #8
    Join Date
    Jul 2009
    Posts
    185
    the findlast is
    expression.FindLast(Criteria) and criteria is like a where is a sql statement so it will not work here. I like the sort by "DESC" seems like the simplest.

  9. #9
    Join Date
    Apr 2009
    Posts
    2
    I've had a similar problem and ended up writing my own function, something like the below. Also use the same in queries etc.

    public function GetMaxDate()

    GetMaxDate= dmax ("field name","table name")

    end function
    Last edited by fed; 07-15-09 at 22:06.

  10. #10
    Join Date
    May 2005
    Posts
    1,191
    What you've done looks pretty similar to Allen Browne's Elookup().
    Me.Geek = True

Posting Permissions

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