Below is one of the query which was adviced from expert-exchange. This query really works fine if there is only one date in it. But, if there are multile dates then this query would fail to identify the oldest record for that day. I have attached the database for your reference. In the table named "LogInOutDetails" there are the following records with no data filled in for "LogOutTiming" column.
UserName LoginTiming LogOutTiming
Prashanth Prabhu 2/7/2012 10:45:47 AM
Prashanth Prabhu 2/8/2012 10:45:47 AM
Prashanth Prabhu 2/8/2012 1:47:19 PM
when I click on "Logout" button on form "PAUT" this query should update the current time for the record "2/8/2012 10:45:47 AM". But this query fails to do. Please advise.
Private Sub Command28_Click()
Dim db As Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Dim Loginout As String
Dim dblhour As Double
Set rst = db.OpenRecordset("LogInOutDetails")
Dim sql As String
sql = "UPDATE LogInOutDetails SET LogInOutDetails.LogoutTiming = #" & Now() & "#" _
& " WHERE (LogInOutDetails.UserName = '" & Forms!PAUT!Text15 & "') AND " _
& "(LogInOutDetails.LoginTiming = #" & DMin("LoginTiming", "LogInOutDetails", "[UserName] = '" & [Forms]![PAUT]![Text15] & "'") & "# AND " _
& " (nz(LogInOutDetails.LogoutTiming,0)=0) AND (DateValue([LogInOutDetails].[LoginTiming]))=#" & Date & "#)"
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.