most recent entry

[markjkubicki]

i really have looked for a resolution on the web (and have found several, often contradictory, and (so it seems to me) far too complex approaches)



my quandary sees to be defining a criteria for the most recent date (see code for a walk through of the difficulty)

as always, with much appreciation in advance,
m.

    
    're-create the table with the current status for all fixture tags
    'if the tag has been submitted, create a record for it's most recent entry in tbl____
    'else create a record in tbl___   (tags not yet submitted)
    
    
    ' loop through all of the Tags in the project that are not VOID
    Set gsDbs = CurrentDb
    Set gsRst = _
        gsDbs.OpenRecordset("SELECT [Tag], [Void] " & _
        "FROM tbeFixtureTagDetails WHERE NOT VOID", _
        dbOpenDynaset)
    
    With gsRst
        DoCmd.GoToRecord , "", acLast
        DoCmd.GoToRecord , "", acFirst
        Do Until .EOF
    
            'check if there is a submittal for the fixture type ([Type])
            blnSubmitted = (DCount("[Tag]", "tbeSubmittalDetails", "[Tag] = '" & !Tag & "'") > 0)
            
            'if there is, then look up the status for the most rescent entry for each [Tag]
            'qrySubmittalDetails contains information about each submittal (Submtl_ID, DateRtrnd...)
            'and of all of the "detail" related to it ([Tag], [Action]...)
            
            If blnSubmitted Then
        
    ' *** IT IS THIS NEXT LINE THAT I AM STUCK ON:
        
                gsCriteria = "[Tag] = '" & !Tag & "' AND [DateRtrnd] = Max("[DateRtrnd]", "qrySubmittalDetails") "
                    
                strID = DLookup("[SubmittalID]", "qrySubmittalDetails", gsCriteria)
                strStatus = Nz(DLookup("[action]", "tbeSubmittalDetails", vCriteria), "unknown")
                           
                '.... add the tag, its current status, the ID of its most recent submittal, and the submittal date...
                
            Else
            
            End If
        Loop
    End With

[June7]

Maybe:

gsCriteria = "[Tag] = '" & !Tag & "' AND [DateRtrnd] = #" & DMax("[DateRtrnd]", "qrySubmittalDetails") & "#"

[Welshgasman]

Also whilst this does not affect this particular issue

        DoCmd.GoToRecord , "", acLast
        DoCmd.GoToRecord , "", acFirst

It is not doing what I think you want it to do? You are moving record position of form records there.
For the recordset you would use

.MoveLast
.MoveFirst

[Gicu]

You need to use dMax not Max:

 gsCriteria = "[Tag] = '" & Me.Tag & "' AND [DateRtrnd] = #" & dMax("[DateRtrnd]", "qrySubmittalDetails","[Tag]='" & Me.Tag & "'")  & "#" 

Cheers,

[ssanfu]

NOTE: "Tag" and "Type" are reserved words and shouldn't be used for object names....