Selecting the right dates in a recordset

[zozew]

Hi all,

I have a Date related problem... Im running this code:

myRecordSet.Open "SELECT tblInmatesProfile.InmateID,tblInmatesProfile.LastName ,tblInmatesProfile.FirstName ,tblInmatesProfile.MiddleName FROM tblInmatesProfile WHERE (((tblInmatesProfile.Archived)=False))"

With myRecordSet
    Do Until .EOF
        intI = 1
        mySubRecordSet.Open "SELECT * FROM tblInmateCases WHERE (((tblInmateCases.InmateID)=" & ![InmateID] & "))"
        
        allCaseNumbers = ""
        allCrimes = ""
        allCaseStatus = ""
        AllImpPen = ""
        AllActImpPen = ""
        AllDateConv = ""
        FullNameString = ![LastName] & ", " & ![FirstName] & " " & ![MiddleName]
        With mySubRecordSet
            Do Until .EOF
                'mySubSubRecordSet.Open "SELECT * FROM tblInmateHearings WHERE (((tblInmateHearings.CaseNumberID)=" & ![CaseNumberID] & "))"
                If intI = 1 Then
                    allCaseNumbers = allCaseNumbers & Nz(![CaseNo], emtyValueRepl)
                    allCrimes = allCrimes & Nz(![Crime], emtyValueRepl)
                    allCaseStatus = allCaseStatus & Nz(![Status], emtyValueRepl)
                    AllImpPen = AllImpPen & Nz(![ImpPen], emtyValueRepl)
                    AllActImpPen = AllActImpPen & Nz(![ActPenImp], emtyValueRepl)
                    AllDateConv = AllDateConv & Nz(![DateConviction], emtyValueRepl)
                Else
                    allCaseNumbers = allCaseNumbers & " / " & Nz(![CaseNo], emtyValueRepl)
                    allCrimes = allCrimes & " / " & Nz(![Crime], emtyValueRepl)
                    allCaseStatus = allCaseStatus & " / " & Nz(![Status], emtyValueRepl)
                    AllImpPen = AllImpPen & " / " & Nz(![ImpPen], emtyValueRepl)
                    AllActImpPen = AllActImpPen & " / " & Nz(![ActPenImp], emtyValueRepl)
                    AllDateConv = AllDateConv & " - " & Nz(![DateConviction], emtyValueRepl)
                End If
                .MoveNext
                intI = intI + 1
            Loop
            .Close
        End With

        Dim SQLString As String
        
        SQLString = "UPDATE tblRegionReport SET CaseNo = '" & allCaseNumbers
        SQLString = SQLString & "', Crime = '" & allCrimes & "', CaseStatus = '" & allCaseStatus
        SQLString = SQLString & "', ImpPen = '" & AllImpPen & "', ActPenImp = '" & AllActImpPen
        SQLString = SQLString & "', DateConv = '" & AllDateConv & "', FullName = '" & FullNameString & "', LastHearings = '" & AllLastHearingDates
        'SQLString = SQLString & "', LastHearings = '" & AllLastHearingDates
    SQLString = SQLString & "' WHERE tblRegionReport![InmateID] = " & ![InmateID] & ";"
        
        db.Execute SQLString
                
        .MoveNext
    Loop
    .Close
End With

And it all works perfect. But....you see the mySubSubRecordSet.open line that is out commented. Well the table tblInmateHearings consists of a bunch of dates past and future for a specific CaseNumberID, and I want to get out the date that is the closest to today's date but not equals to today's date or past today's date. And the result beeing a variable for each InmateID containing one Date specified above for each CaseNumberID there is....

Example:
InmateID 5 has three CaseNumberID's. The desirable result then would be a variable like "AllLastHearingsDates" (out commented in the SQLString variable above) beeing set to "1/1/2011, 5/2/2011, 6/2011". So three caseNumberID makes three dates in the "AllLastHearingsDates" variable even though each caseNumberID has a bunch of dates linked to it in the tblInmateHearings.


So any ideas on how to achieve this? I think i need another With loop (thats why i put the mySubSubRecordSet.open line there) to loop through the dates just like the other two recordsets but im not sure how to define the VBA to get the speific dates im talking about..

Thanks for any input...

ps. The resulting SQL populates a table that i make a report from. That's why i need all these dates in a string variable

 

[pbaldy]

I would think you could get it with SQL like:

SELECT Max(DateField) AS LastDate
FROM TableName
WHERE KeyField = Whatever AND DateField < Date()

 

[zozew]

Well...well....how easy was that Thank you so much for easing my aching brain.

My keyboard sends its regards and thanks you for making me stop taking out my frustration on it.

thx

Zozew

 

[pbaldy]

LOL! Happy to help.