Hours in a form and report

[Guest]

Hi,
I have a table where it has a field Time-In and aother field Time-out, these
ar for the employees worked hours.
I also created another Table named 'Time" with two fields, 'Time-In and
Time-out" , there I entered the text 7:00 am all the way til' 21:00 pm in
both fields. (not sure if neccessary thoigh).
I have a form where besides customer info the user will have two combo boxes
to choose time-in and time-out.
I just don't know how to create a text box with the total time worked
between the time-in and time-ou. Is this possible?
Also, I need to include the total hours worked in a report.
Could somebody help me with this project please?
I'm not good with VB, It's confusing.
Please be very specific if possible.

Thank you guys.

 

[John W. Vinson]

Hi,
I have a table where it has a field Time-In and aother field Time-out, these
ar for the employees worked hours.
I also created another Table named 'Time" with two fields, 'Time-In and
Time-out" , there I entered the text 7:00 am all the way til' 21:00 pm in
both fields. (not sure if neccessary thoigh).
I have a form where besides customer info the user will have two combo boxes
to choose time-in and time-out.
I just don't know how to create a text box with the total time worked
between the time-in and time-ou. Is this possible?
Also, I need to include the total hours worked in a report.
Could somebody help me with this project please?
I'm not good with VB, It's confusing.
Please be very specific if possible.

Thank you guys.

Don't store the duration at all, anywhere.

Instead you can just store the time in and the time out, and calculate the
hours on demand.

For integer (whole number) hours, use

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

as the Control Source of a textbox, or as a calculated field in a query. For
hours and fractions of an hour (e.g. 8.75 hours from 8:00am to 4:45pm) use

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

Note that a Date/Time value is actually stored as a number, a count of days
and fractions of a day since midnight, December 30, 1899. Putting JUST a time
value into a field means it's a time on that long-ago day, and that there is
no way to distinguish 8:00am on April 4 from 8:00am on May 30th. You may want
to consider storing the date and time together in the same fields.

No VBA is needed (at least not so far....)

John W. Vinson [MVP]

 

[Guest]

John,
Thanks for your help. That was it. Simple and easy to understand.

Thank you again.

Another question.
I have a table name Employees,. And in the "Main" form I have to enter the
name of employees who worked in a job.
I want to be able to have a list of the employees and put a check mark for
all the employees who worked in a project.
Is there an alternative way to do this?

thank you.

Hi,
I have a table where it has a field Time-In and aother field Time-out, these
ar for the employees worked hours.
I also created another Table named 'Time" with two fields, 'Time-In and
Time-out" , there I entered the text 7:00 am all the way til' 21:00 pm in
both fields. (not sure if neccessary thoigh).
I have a form where besides customer info the user will have two combo boxes
to choose time-in and time-out.
I just don't know how to create a text box with the total time worked
between the time-in and time-ou. Is this possible?
Also, I need to include the total hours worked in a report.
Could somebody help me with this project please?
I'm not good with VB, It's confusing.
Please be very specific if possible.

Thank you guys.

Don't store the duration at all, anywhere.

Instead you can just store the time in and the time out, and calculate the
hours on demand.

For integer (whole number) hours, use

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

as the Control Source of a textbox, or as a calculated field in a query. For
hours and fractions of an hour (e.g. 8.75 hours from 8:00am to 4:45pm) use

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

Note that a Date/Time value is actually stored as a number, a count of days
and fractions of a day since midnight, December 30, 1899. Putting JUST a time
value into a field means it's a time on that long-ago day, and that there is
no way to distinguish 8:00am on April 4 from 8:00am on May 30th. You may want
to consider storing the date and time together in the same fields.

No VBA is needed (at least not so far....)

John W. Vinson [MVP]

 

[John W. Vinson]

John,
Thanks for your help. That was it. Simple and easy to understand.

Thank you again.

Another question.
I have a table name Employees,. And in the "Main" form I have to enter the
name of employees who worked in a job.
I want to be able to have a list of the employees and put a check mark for
all the employees who worked in a project.
Is there an alternative way to do this?

Well, I can think of several.

If you have Employees and Jobs, and each employee can work on several jobs,
and each job might need several employees, you need *three* tables:

Employees
EmployeeID
LastName
FirstName
<other biographical info>

Jobs
JobID <primary key, maybe autonumber, maybe something else>
JobName
Description
<other info about the job, e.g. start date, end date, ...>

JobAssignments
EmployeeID <link to Employees>
JobID <link to Jobs>
<fields about this person's involvement with this job, e.g. role, date
started, time spent, ...>

You can use a Form based on Employees, with a subform based on JobAssignments
to enter a new record in JobAssignments for each job that the employee works
on; alternatively - or in addition - you could have a form based on Jobs a
subform, again based on JobAssignments. In either case the subform would have
a combo box to select the Job (or Employee).

John W. Vinson [MVP]