how to select records from a table where date and time are last -Ms Access 2007

[Emmanuel Ayivi]

I created a table called "tblfeesdetails" with fields; "groupid", "Academic_year", "Term", "Insect_date and time" and these fields are updated base on a command button click and the update date and time recorded in the field "Insect_date and time". The records in table "tblfeesdetails" has date 1, date 2,... and date 9 base on the update from the command button. Now, I want select all from the table "tblfeesdetails" where Insect_date and time = 9 or has the last date and time

This is the code i used

 strsqldetails = "Select * from tblfeesdetails where groupid = """ & mgroupid & """; "

[NeoPa]

This makes little sense when you say you want to specify the results either match a specific Group ID or match the last Date/Time found. Do you mean both must match (The record with the last Date/Time for the specified group)?

[Emmanuel Ayivi]

I mean both must match (The record with the last Date/Time for the specified group)

System Error = type mismatch

strsqldetails = "Select * from tblfeesdetails where groupid = """ & mgroupid & """; "

[NeoPa]

Try this instead then :

strSQLDetails = "SELECT tFD.* " & _
                "FROM   [tblFeesDetails] AS tFD" & _
                "       INNER JOIN" & _
                "      (SELECT LAST([Insect_Date and Time]) AS [LastDT]" & _
                "       FROM   [tblFeesDetails]" & _
                "       WHERE  ([GroupID] = '%G'")) AS sQ" & _
                "  ON   tFD.[Insect_Date and Time] = sQ.LastDT " & _
                "WHERE  (tFD.GroupID = '%G'")
strSQLDetails = Replace(strSQLDetails, "%G", mGroupID)

This uses an embedded subquery that has the same filtering for the Group ID.

[NeoPa]

Actually, that's the basic SQL sorted out, but it seems you may be dealing with a [GroupID] that isn't a text string (from the reported error message in your latest post - information that should also have been in the original question). In that case you need to remove the single quotes (') from the code. Single quotes indicate that the value between them should be treated as a text literal.