troubleshooting Question

Update the date and time for recent record for today's date

Avatar of pg1533
pg1533 asked on
Microsoft AccessVisual Basic Classic
9 Comments1 Solution426 ViewsLast Modified:

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 & "#)"
    DoCmd.SetWarnings False
    DoCmd.RunSQL sql
    DoCmd.Quit acQuitSaveAll
    End Sub
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros