UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Defeat On Building A Time Card, Access 2016    
 
   
wornout
post Jul 8 2017, 03:41 PM
Post#1



Posts: 953
Joined: 17-November 13
From: Orewa New Zealand


I have spent 3 weeks trying to build a time card and I still don't seem to be able to build something right
does anybody know of a time card that I can have a start time a finish time to the second and then total the hours and have the total as a number so I can pop totals hours in another form and do some calculations with it.
or guid me on how to build it or something
I have never admitted defeat before but this one has me beat
it will need to have on it the taskid and client both of winch will come through from my tasks form when time card is open
the current date
start time
finish time
hours worked for that entry
the hours worked for that date
the hours worked for that task
all as numbers
even if someone can tell me how to get start and finish time as numbers or the best way to do start and finish
Go to the top of the page
 
GroverParkGeorge
post Jul 8 2017, 04:16 PM
Post#2


UA Admin
Posts: 30,758
Joined: 20-June 02
From: Newcastle, WA


The short answer is that you don't store the start and end times as numbers. They are CLOCK times.

To get the difference between them, on the other hand, you simply use DateDiff() and thatELAPSED time is your number. Do you know how to use DateDiff()?

--------------------
Go to the top of the page
 
MadPiet
post Jul 8 2017, 05:01 PM
Post#3



Posts: 2,185
Joined: 27-February 09



There's almost nothing to a timecard system.

(EmployeeID, DateTimeIn, DateTimeOut, TaskWorkedOnID) and then you make sure an employee can't have overlapping worktimes - (otherwise he's being paid twice for a single time range).
Go to the top of the page
 
mklein
post Jul 8 2017, 05:11 PM
Post#4



Posts: 255
Joined: 7-August 12
From: BC, Canada


I probably wouldn't store the start AND finish times in the same record. Imagine a table like...

tblPunch
PunchID
EmployeeID
Direction (-1 for in, +1 for out)
DateTime

... and then to add up the time you can just do...

CODE
SELECT Sum(Direction * DateTime) As TotalHours, Count(*) As PunchCount
FROM tPunch
WHERE EmployeeID = 1234
   AND DateValue(DateTime) = #7/8/2017#

...so you can see that storing the data that way suddenly gives you a very simple way to add up the hours, by employee, by date, by week, by any time period really. And easy to error check. If TotalHours is negative you have too many punches in. If PunchCount is odd you are missing a punch. If TotalHours is way too high you have too many punches out.

So I would think of the time card punch event as a discrete object, and give the one punch a whole row in a table, and see if it offers you a bunch of wiggle room you weren't finding doing it other ways.

hth
Mark

--------------------
| Mark Klein | Access 2010 | Windows 10 | Visual Studio 2013
Go to the top of the page
 
wornout
post Jul 8 2017, 05:31 PM
Post#5



Posts: 953
Joined: 17-November 13
From: Orewa New Zealand


ok here is a paired down version in the VBA I have commented out all the stuff that points to the tasks table
when you open the form
if you click on the new button you can click on start time
you have to wait 1 minute before you can click on end time
if you click on new time
and then tick the checkbox just hours you can write in just the hours in the format 300 for 3 hours then hit enter this will log the hours to total hours
so the problems I am having is
1 it keeps putting blank times in the datasheet below
2 the total hours dont stay in the below data sheet
3 I need it to come out looking like the total hours in the textbox on the form but with . not : so like 15:01 should be 15.01 as a number
then when I have got this right I can make an updateable query with tasks and time table joined on taskid
This post has been edited by wornout: Jul 8 2017, 05:34 PM
Attached File(s)
Attached File  Database42.zip ( 39.84K )Number of downloads: 5
 
Go to the top of the page
 
GroverParkGeorge
post Jul 8 2017, 07:13 PM
Post#6


UA Admin
Posts: 30,758
Joined: 20-June 02
From: Newcastle, WA


As I've tried to explain at least twice in this thread and another, ELAPSED TIME IS A NUMBER; IT IS NOT A CLOCK TIME.

Attached File  ElapsedTimeError.png ( 24.35K )Number of downloads: 0


You are still saving "Hours" field as a Date/Time value and formatting it as short time, so that it DISPLAYS a value to mimic an elapsed time.

It is actually a CLOCK time, and that means any math you try to do with it is bogus.

A lot of the code you are using to "parse" your times is based on the problems created by this fundamental error.

Another issue here is that you are apparently storing Hours, which is a calculated field. It's generally undesirable to store calculated fields.

--------------------
Go to the top of the page
 
wornout
post Jul 8 2017, 07:19 PM
Post#7



Posts: 953
Joined: 17-November 13
From: Orewa New Zealand


Ok sorry I did not understand you before and I still dont .I am not sure how to fix it. I want to store the total hours so I can use it later in an update able query and if I join 2 querys one with a equation I can not up date it through my form
Do I just change the hours format in the table?
when I say update able query I mean one I can do data entry on
you keep telling me whats wrong but I dont know how to fix it
This post has been edited by wornout: Jul 8 2017, 07:20 PM
Go to the top of the page
 
tina t
post Jul 8 2017, 08:52 PM
Post#8



Posts: 5,169
Joined: 11-November 10
From: SoCal, USA


QUOTE
tblPunch
PunchID
EmployeeID
Direction (-1 for in, +1 for out)
DateTime

Mark, what is the data type of the DateTime field in your example? i just set up the table and query you posted, making field DateTime a Date/Time field, and did not get a valid return of hours from the query, so...?

also, what about shift employees, who often start a shift in one day's PM, and end the shift in the next day's AM?

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
wornout
post Jul 8 2017, 08:54 PM
Post#9



Posts: 953
Joined: 17-November 13
From: Orewa New Zealand


Right so I have my datediff have had it all along
Hours =DateDiff("n", Starttime, Finishtime) \ 24 & "." & Format(DateDiff("n", [Starttime], [Finishtime]) Mod 24, 0)
so how do I get it to add the textbox when I manually put hours in
just to make that clear
I have the 2 time ones and then a text box (text82
I did try if text82 <0 then
hours = the date diff
else
hours = Text82
but it did not change when I put a new record in it changed the whole column to that number
Go to the top of the page
 
GroverParkGeorge
post Jul 8 2017, 09:05 PM
Post#10


UA Admin
Posts: 30,758
Joined: 20-June 02
From: Newcastle, WA


Here's one way to do this. There are others, which have been suggested.

Note that the code is VERY much simplified by using appropriate datatypes for dates, times and elapsed times.

Attached File  Database42.zip ( 42.25K )Number of downloads: 4

--------------------
Go to the top of the page
 
GroverParkGeorge
post Jul 8 2017, 09:06 PM
Post#11


UA Admin
Posts: 30,758
Joined: 20-June 02
From: Newcastle, WA


If you need to do further calculations with hours, total hours, etc. you simply recalculate them.

--------------------
Go to the top of the page
 
mklein
post Jul 9 2017, 12:08 PM
Post#12



Posts: 255
Joined: 7-August 12
From: BC, Canada


Tina:
The query should return a double, and it is the number of hours expressed as a datetime value since midnight 12/30/1899--which is the zero date--so treat it as a date type. That being the case, if employee1 starts on 7/9/2017 at noon, and ends at 7/10/2017 at midnight, which would be a 12 hour shift, the query would return 0.5. To convert to hours, multiply by 24.

CODE
SELECT Sum(Direction * DateTime * 24) AS TotalHours
FROM ...

The case where a shift spans midnight: the above math works just fine. The problem of calculating hours for a shift that spans the end of a pay-period is, of course, a problem you will have regardless of how you store the data.

The nice thing about this approach is that you can have numerous punch ins and outs, and that's where I developed this system, because I had a customer where employees punched out for lunch, and/or to go to the dentist, and then back in, so there might be 6 punches, 3 in, 3 out, for one employee in a day, and the query above does that math without batting an eye. It's also easy to find missing data, because if a punch is missing, the result is not in the range of 0.00 to 0.40 it is in the range of CLng(Date()), which, today, is 42925, and if the punch out is missing, that number is negative. If a punch in is missing, that value is positive.

Does that make sense?
Mark

--------------------
| Mark Klein | Access 2010 | Windows 10 | Visual Studio 2013
Go to the top of the page
 
tina t
post Jul 9 2017, 12:55 PM
Post#13



Posts: 5,169
Joined: 11-November 10
From: SoCal, USA


@Mark
got it, thank you! :) tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
wornout
post Jul 9 2017, 03:45 PM
Post#14



Posts: 953
Joined: 17-November 13
From: Orewa New Zealand


Well I did not give up and I did it
I still don't grasp dates and times very well
Thanks For everyone's help
I have attached my database just in case someone has some more helpful tips I am using it its not perfect but it does the job for keeping track of time spent and I can put my own manual hours in.
Just be aware that I am self taught with the help of all you wonderful people
Attached File(s)
Attached File  Database4.zip ( 210.13K )Number of downloads: 8
 
Go to the top of the page
 
tina t
post Jul 9 2017, 04:18 PM
Post#15



Posts: 5,169
Joined: 11-November 10
From: SoCal, USA


QUOTE
Just be aware that I am self taught with the help of all you wonderful people

no worries - some of the wonderful people, and a lot of the rest of us too, are self-taught also. :) tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
MadPiet
post Jul 9 2017, 11:22 PM
Post#16



Posts: 2,185
Joined: 27-February 09



QUOTE
I still don't grasp dates and times very well


If you think of dates as "whole numbers" on a number line, then times between two dates are fractional numbers between one date and another. Then you can use DATEADD() to move up and down the time line (date number line) a chosen number of units.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2017 - 05:31 PM