Using Hour Data Within a General Date 2

[JoyousL]

I am creating an hour tracking program for employees to "clock-in" and "clock-out". The forms and tables are complete, but I’m having trouble with the date/time data. On my clock-in/clock-out forms, users click a button, and a field is populated with the current date and time. I have formatted the table and forms to show the date and time in one field, and used the “Me.FieldName = Now()” code. Is there a way to use the date and time portions separately? For instance, I want to know the total hours worked in a shift, and eventually I will create a form to view the hours worked for each employee by day. Any help will be greatly appreciated.

Thanks!
Joy Lytle

 

[PHV]

DatePortion = Int(DateTimeField)
TimePortion = DateTimeField - Int(DateTimeField)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Joy Lytle

Date/Time values are just NUMBERS.

Right NOW is 38308.36299. The integral part is the date. The fractional part is the time.

so
[tt]
Format(38308, "yyyy/mm/dd") is 2004/11/17
Format(.36299 "hh:nn:ss") is 08:44:44
Format(38308.36517 "ddd mmmm dd hh:nn") is Tue November 17 08:44
[/tt]

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[JoyousL]

I am learning as I go, and I'm still trying to figure this part out... would you mind going into a little more detail on where I should use this information?
Thanks!
Joy

 

[SkipVought]

For instance is ClockIn and ClockOut is Date/Time, then if your Time Transaction table were something like this...
[tt]
EmplID
ClockIn
ClockOut
[/tt]
then the daily time worked is

Select EmplID, Format(ClockIn, "yyyy/mm/dd"), sum(ClockOut-ClockIn)*24 As Hrs
From tblTimeTrans
Group By EmplID, Format(ClockIn, "yyyy/mm/dd")

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[JoyousL]

Again, sorry for being so new at this. I understand what the code is going to do as far as formatting, and the sum command etc. However, I'm still not sure where to put it. Would I place this code in a query, or in an after update event for the total hours field on my form...?
Joy

 

[SkipVought]

This is an axample of a query. If you are using the query grid, then a single filed element would be
[tt]
Format(ClockIn, "yyyy/mm/dd")
[/tt]
for instance.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[JoyousL]

Skip,

I'm still having some trouble with the "sum". So, let me explain how I have my data set-up, and how I am populating my form.

I have the following table: tblClockIn

PunchInID (auto number)
EmployeeID (Linked to a main employee info table)
TimeIn (formatted as General Date to show Date and Time)
TimOut (formatted as General Date to show Date and Time)
TotalHours (where I am trying to sum)

I have a main form, frmClockInOut, where the employee selects their ID, and once the ID is selected, a subform is populated with their information. This subform also houses two subsub forms: the clockIn sub sub form, and the ClockOut sub sub form.

The ClockOut sub sub form is where I am trying to "sum", and it pulls info from tblClockIn via a query using the PunchInID. The form has fields showing the clock-in time, clock-out time, and sum. (If you need more information of how the query is set-up, I can give that to you)

I tried re-formatting the fields like you said earlier, and I couldn't make it pull the right data. So, in the query, I made an expression field for the sum: "TimOut-TimeIn" and it did give me a value, but they all look something like this: 5.787E-07.

Given this information on the way I have things set-up, can I use the date and time information separately, and if so how would I sum? Do I need to create a new query with the code you gave earlier? Do I need to start over ?

Thank you in advance for your help.
Joy

 

[SkipVought]

What were two TimOut TimeIn values that gave you 5.787E-07.

I'd make the table
[tt]
PunchInID (auto number)
EmployeeID (Linked to a main employee info table)
TimeIn (formatted as General Date to show Date and Time)
TimOut (formatted as General Date to show Date and Time)
[/tt]
and the query

Select EmployeeID, Format(TimeIn, "yyyy/mm/dd"), sum(TimOut-TimeIn)*24 As Hrs
From tblClockIn
Group By EmployeeID, Format(TimeIn, "yyyy/mm/dd")

Its not a good design to store aggregate data -- it is DUPLICATION. You REPORT aggregations.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[JoyousL]

I couldn't find the entry that I cut that from...but here is another example:

TimeIn = 3:14
TimOut = 3:14
Sum = 3.472E-05

but if I wait a little longer:

TimeIn = 3:16
TimOut = 3:18
Sum = .00125

 

[SkipVought]

oops. SUM in your query.

Please show me the calculation that you are using.

I get .0333 hrs which is 2 minutes.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[JoyousL]

OK, so I created a query with the code that you sent, and I am pretty sure that it is now pulling the time, and giving me the total hours worked.
Thank you for all of your help. I need to spend some time with this, and get my head around it.
Joy

 

[SkipVought]

Just remember that the units of TIME are DAYS.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}