Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    5

    Unanswered: Find Second Most Recent Date

    I'm pretty new to VBA, i'm trying to fill out a form using information from the most recent date and the next most recent date of a table. I need the name of the table to be a variable, which seems to be causing problems. Right now i have

    Dim StudentName As String

    MostRecentDate = DMax("TestDate", StudentName)

    NextDate = DMax("TestDate", StudentName, "TestDate <> MostRecentDate")


    The mostrecentdate works, the second works if the name of the domain is not a variable. There will never be duplicate dates, i'm not worried about it skipping entries with the same date.

    Right now, each student has a table with test date rows, and test type columns. I need the dates so i can scan through the table with

    With rstStudent
    Do Until .EOF
    For Each fldTestDate In .Fields
    If fldTestDate.Name = "TestDate" Then
    If fldTestDate.Value = MostRecentDate Then

    'fill form with info from this row

    Exit Sub
    End If
    End If
    Next
    .MoveNext
    Loop
    End With

    Any ideas?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should be:
    Code:
    NextDate = DMax("TestDate", StudentName, "TestDate <> #" & MostRecentDate & "#")
    The way your code is written, MostRecentDate is litteral as it is included in quotes.
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    5
    thank you so much, i hoped it was something easy like that

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

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