We help IT Professionals succeed at work.

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

418 Views
Last Modified: 2012-02-16
Hi,

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
Copy-of-latest.zip
Comment
Watch Question

Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
What error do you get ?

Author

Commented:
I will not get any error. The query gets executed correctly. But, the query would not update the current date and time for the record where the log in timing is 2/8/2012 10:45:47 AM if there are different dates.

However, if the table has data with only today's date then this query would work perfectly. But, if the table has data with different date then it would not.

Table example with only 02/08/2012 - query executes perfectly

UserName                      LoginTiming                      LogOutTiming
Prashanth Prabhu      2/8/2012 10:45:47 AM
Prashanth Prabhu      2/8/2012 1:47:19 PM

Table with 02/08/2012 and 02/07/2012 - nothing gets updated, no error in executing. But none of the records get updated.

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

Please advise
CERTIFIED EXPERT
Top Expert 2016

Commented:
i think there is something wrong with your criteria, try this select query and see if you get something to show


SELECT LogInOutDetails.UserName, LogInOutDetails.LoginTiming, LogInOutDetails.LogoutTiming
FROM LogInOutDetails
WHERE (((LogInOutDetails.UserName)="Prashanth Prabhu") AND ((LogInOutDetails.LoginTiming)=DMin("LoginTiming","LogInOutDetails","UserName ='Prashanth Prabhu'")) AND ((nz([LogoutTiming],0))=0))
ORDER BY LogInOutDetails.UserName, LogInOutDetails.LoginTiming, LogInOutDetails.LogoutTiming;


now, can you spell out what you want to do (in plain english)

i want to update the field ______

Author

Commented:
Data which I have now is

Table: LogInOutDetails

UserName              LoginTiming                    LogOutTiming
Prashanth Prabhu      2/7/2012 8:00:56 AM
Prashanth Prabhu      2/8/2012 8:00:56 AM
Prashanth Prabhu      2/8/2012 8:15:56 AM

When I run this query it is giving 'Prashanth Prabhu      2/8/2012 8:00:56 AM' as the result which is correct.

I want to update the field "LogOutTiming" with the current date and time. Once this is updated then the hour difference between the "LogInTiming" and "LogOutTiming" updated in the column name "TotalTime". Total time is the additional column in "LogInOutDetails" table. Hope I am clear now.
CERTIFIED EXPERT
Top Expert 2016

Commented:
run this query, see if this will update the record  
Prashanth Prabhu      2/8/2012 8:00:56 AM



UPDATE LogInOutDetails SET LogInOutDetails.LogoutTiming = Now()
WHERE (((LogInOutDetails.UserName)="Prashanth Prabhu") AND ((LogInOutDetails.LoginTiming)=DMin("LoginTiming","LogInOutDetails","UserName ='Prashanth Prabhu'  AND (LogInOutDetails.LogoutTiming) Is Null And DateValue([LoginTiming])= Date()")));

Author

Commented:
Yes, this is updating the record

Prashanth Prabhu      2/8/2012 8:00:56 AM

Now the data in the table is as given below

UserName              LoginTiming                      LogoutTiming                          TotalTime      
Prashanth Prabhu      2/8/2012 8:00:56 AM      2/8/2012 11:35:59 PM      

Now, the "TotalTime" column should be updated. Basically, it is the difference between the "LogOutTiming" and "LogInTiming". This should be displayed in hours. Please help.

Thanks,
Prashanth

Author

Commented:
Also, in the query the UserName should be based on the value which is in the Text15 in form 'PAUT'

Author

Commented:
how would I call query which is created in vba code?
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

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.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.