DateDiff on Several days

  • Thread starter Thread starter Jose
  • Start date Start date
J

Jose

I have a volunteers database that has tables "contact" and "attendance". The
attendance and contact tables are linked through a querry as was done in the
Access 2007 students template between "students" and "attendance".

In my database I am trying to display in a form the attendance and duration
of attendance for each volunteer but I have been unable to do so.
My Attendance table has columns (ID, Volunteer,Day, TimeIn, TimeOut). Day
can be any of 3 days between Friday through Sunday. And TimeIn and Out are
date/time fields in medium time format.
Each volunteer can volunteer any of those 3 days and their time in and out
will be entered respectively. I have a form that shows the volunteers
contact info, and a subform with their attendance record. Everything works
fine, but what I am trying to do is to display the total hours each volunteer
worked each day, and in total (add all hours worked on the 3 days.) I have
read many things on how to use the datediff function, but so far I have not
been able to figure out how to display all these values in my form/subform.
All help with this issue is greatly appreciated!
 
Drop the 'Day' field and use DateTime field for TimeIn and TimeOut.

DateDiff consist of these parts --
DateDiff( "Unit Designator", [firstdate], [second date] )
Unit Designator
d - day
h - hour
n - minute
s - second
w - week
m - month
q - quarter
yyyy - year

If you are wanting hours & minutes then you need to use minutes and then
parse like this --
Time Visited: [MyDateDif]\60 &":"& [MyDateDif] Mod 60
 
Thanks for your Reply Karl.

I was able to add a text box to the subform that can be displayed in the
Main Form that displays the number of hours volunteered each day. I know you
said to remove the day field but it works as if each day displays a different
amount of time depending on the timein time out fields. It looks as if the
text box I created in the subform is displayed as regular fields. (tabular
format)

However I can't figure out how to display in a text box (single value) the
sum of the hours worked each day for each volunteer.

This is one of the last few steps I need to take before finishing the
database. I need all the help I can get.

Thanks

KARL DEWEY said:
Drop the 'Day' field and use DateTime field for TimeIn and TimeOut.

DateDiff consist of these parts --
DateDiff( "Unit Designator", [firstdate], [second date] )
Unit Designator
d - day
h - hour
n - minute
s - second
w - week
m - month
q - quarter
yyyy - year

If you are wanting hours & minutes then you need to use minutes and then
parse like this --
Time Visited: [MyDateDif]\60 &":"& [MyDateDif] Mod 60

Jose said:
I have a volunteers database that has tables "contact" and "attendance". The
attendance and contact tables are linked through a querry as was done in the
Access 2007 students template between "students" and "attendance".

In my database I am trying to display in a form the attendance and duration
of attendance for each volunteer but I have been unable to do so.
My Attendance table has columns (ID, Volunteer,Day, TimeIn, TimeOut). Day
can be any of 3 days between Friday through Sunday. And TimeIn and Out are
date/time fields in medium time format.
Each volunteer can volunteer any of those 3 days and their time in and out
will be entered respectively. I have a form that shows the volunteers
contact info, and a subform with their attendance record. Everything works
fine, but what I am trying to do is to display the total hours each volunteer
worked each day, and in total (add all hours worked on the 3 days.) I have
read many things on how to use the datediff function, but so far I have not
been able to figure out how to display all these values in my form/subform.
All help with this issue is greatly appreciated!
Click to expand...
Click to expand...
 
Back
Top