DateDiff

[MS_Access_Amature]

I have a Table (Employees) with the following fields: EmployeeID, EmployeeName, DateOfWork, TimeIn, TimeOut, and DayTotal.

My form has a listbox (ListEmployees) and a ButtonPunchIn and a ButtonPunchOut.

Everything is working. But what I want is to add to the ButtonPunchOut On click procedure. I want it to calculate the time that employee worked that day. So in other words i want to see how long an employee worked that day using the DateDiff between TimeIn and TimeOut and putting that Value in the DayTotal field. My problem is i dont know what to put after DateDiff.

Private Sub ButtonPunchOut_Click()
Dim db As DAO.Database, strMyValue As String, datMyValue As Date
strMyValue = Me.listEmployee
datMyValue = Now()
Set db = CurrentDb
db.Execute "Update Employee set TimeOut=Now() where EmployeeName=('" & strMyValue & "') "

Dim Total as Integer
Total = DateDiff("n",?????)

End Sub

Can someone please explain to me. Or should i use a different function??

 

[John Big Booty]

Your DateDiff() should look like;

DateDiff("n", [TimeIn],[TimeOut])

 

[MS_Access_Amature]

It gives me an error saying it can't find the field '|' referred to in my expression???

 

[John Big Booty]

Sorry try;

DateDiff("n", Me.TimeIn,Me.TimeOut)

I'm presuming that the fields TimeIn and TimeOut appear on your form.

 

[boblarson]

If TimeIn And TimeOut are FIELDS that are in the form's recordsource (they must be in order for this to work):

Dim lngTotal As [B]LONG[/B]
lngTotal:DateDiff("n", Me!TimeIn,Me!TimeOut)

So also you will note that I changed your variable to LONG (which is what date/time is and I suggest that so you don't have an overflow error for some reason. And I named my variable lngTotal instead of Total, which if you do that it will help you avoid using Access Reserved Words accidentally. Now Total isn't an Access Reserved Word but if you get in the habit of using the prefixes then you will not have to worrry about it at all.

 

[boblarson]

Sorry try;

DateDiff("n", Me.TimeIn,Me.TimeOut)

I'm presuming that the fields TimeIn and TimeOut appear on your form.

That would be my assumption too. You will notice that I posted just after you but I took it one step further by using the ! instead of dot which lets me reference the FIELD in the recordsource and it doesn't even need to be in a control on the form.

 

[MS_Access_Amature]

my form's recordsource is a different table that's linked called UsernamePassword

But the table that the records get recorded to is Employees. So the ButtonPunchIn and PunchOut are in the form with the UsernamePassword record source but the time recordes goes into the employees table. So thats why TimeIn and TimeOut don't get recognized??

 

[John Big Booty]

Thanks Bob, that's a useful thing I've learned today.

Now I wonder what it was I just forgot so I could make room for it