Calculate Date Difference

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
D

Dave Elliott

Here is my Code to calculate the Total Time per Employee, however it does
not Total for all the Employees Hours. I need it to total time for each
employee showing all their time.
The Report is based on a query named TimeClock which has 4 fields, Driver,
loginDate, loginTime, logoffTime.
How can I make it total correctly ?



=DateDiff("n",[loginTime],[logoffTime])/60


Thanks,

Dave
 
Dave Elliott said:
Here is my Code to calculate the Total Time per Employee, however it does
not Total for all the Employees Hours. I need it to total time for each
employee showing all their time.
The Report is based on a query named TimeClock which has 4 fields, Driver,
loginDate, loginTime, logoffTime.
How can I make it total correctly ?



=DateDiff("n",[loginTime],[logoffTime])/60
Click to expand...

You need two fields that have both the date and the time in them for this to be
accurate. Do your fields have this? If they don't your expression would only work
when loginTime and logoffTime occur on the same day. Is that always true? If your
fields either do include a date value or they will always be recording the time on
the same day, then I can't see why the result would not be accurate.
 
Dave Elliott said:
loginTime and logoffTime are the same field type's.
They are both medium time fields.
There is only one date field, named loginDate
loginTime and logoffTime should be on the same day, but not always.
Click to expand...

Well when they *are* on the same day I don't see why the DateDiff() would not work.
What exactly is wrong with the result you're getting.

In the Access Immediate window...

?DateDiff("n", #7:00#, #9:00#)

....gives me 120.

You should be aware too that there is no such thing as a "medium time field". That
is a display format. An Access DateTime field always includes both a date and a
time. If the time is not entered it defaults to Midnight and if a date is not
entered it defaults to 12/30/1899. That being the case my DateDiff example above is
actually returning the number of minutes between 12/30/1899 7:00:00 and 12/30/1899
9:00:00.

If you are entering time only and you want the later time to be after midnight
DateDiff() will fail because it will see the second time as occurring before the
first, not after. If you simply used two fields that included the date and time of
login and the date and time of logoff, then DateDiff() should work correctly.
 
I dont get a Gross Total for each Employee, it totals up for all Employees.
I need an expression of code that will total for each Employee. Sum, etc...
I need this code to total for each Employee.
Need for Form and Report.
=DateDiff("n",[loginTime],[logoffTime])/60
 
Dave Elliott said:
I dont get a Gross Total for each Employee, it totals up for all Employees.
I need an expression of code that will total for each Employee. Sum, etc...
I need this code to total for each Employee.
Need for Form and Report.
=DateDiff("n",[loginTime],[logoffTime])/60
Click to expand...

Is this a continuous form? Where on your form is the control having this
expression?
 
Yes, it is a continuous form.
It is located in the details section and the below code is located in the
form footer.

=Sum([Minutes])\60 & Format(Sum([Minutes]) Mod 60,"\:00")




Rick Brandt said:
Dave Elliott said:
I dont get a Gross Total for each Employee, it totals up for all Employees.
I need an expression of code that will total for each Employee. Sum, etc...
I need this code to total for each Employee.
Need for Form and Report.
=DateDiff("n",[loginTime],[logoffTime])/60
Click to expand...

Is this a continuous form? Where on your form is the control having this
expression?
Click to expand...
 
Can send zipped DB if this will help. Small, just 328 kb without zipping.

Rick Brandt said:
Dave Elliott said:
I dont get a Gross Total for each Employee, it totals up for all Employees.
I need an expression of code that will total for each Employee. Sum, etc...
I need this code to total for each Employee.
Need for Form and Report.
=DateDiff("n",[loginTime],[logoffTime])/60
Click to expand...

Is this a continuous form? Where on your form is the control having this
expression?
Click to expand...
 
Dave Elliott said:
Yes, it is a continuous form.
It is located in the details section and the below code is located in the
form footer.

=Sum([Minutes])\60 & Format(Sum([Minutes]) Mod 60,"\:00")
Click to expand...

You say code, but do you mean that you are using the above as the ControlSource
for a TextBox in the form footer? If so then that is what the control will do.
Display the sum for *All* of the records currently in the forms detail section.
If you want it to sum for only one Person, then you need to filter the form so
it is only displaying one person's records.
 
Back
Top