I have designed a "Time Tracker Database"... Basically a Time Clock. I
have report that calculates the number of regular hours worked per
week.
Currently I am running the following query:
----------------------------------------------------------------------
SELECT DateFilter.Empl oyee, DateFilter.Time TypeIn,
Sum([ClockOut]-[ClockIn]) AS Expr1
FROM DateFilter
GROUP BY DateFilter.Empl oyee, DateFilter.Time TypeIn
HAVING (((DateFilter.T imeTypeIn)="Reg ular"));
ClockIn & ClockOut are both Time/Date
----------------------------------------------------------------------
Everything calculates correctly up to 24 hours. After that, it messes
up. I would like the sum to say something like 38:25:02
Any suggestions would be appreciated! 10 3357
Do the calculation in the smallest units you want to report. For example, if
you want hours and minutes, do the calculation in minutes. Format it later
the way you want it after you get the total. Instead of subtracting ClockIn
from ClockOut, use the DateDiff function and tell it to return minutes (n).
In your example, it appears you're wanting to track to the second, is that
correct?
Example:
TotalTime:Sum(D ateDiff("s", [ClockIn], [ClockOut])) \ 3600 &
Format((Sum(Dat eDiff("s", [ClockIn], [ClockOut])) Mod 3600) / 86400,
":nn:ss")
Example from the Immediate window:
?10000 \ 3600 & Format((10000 Mod 3600) / 86400, ":nn:ss")
2:46:40
The problem you're running into is that you want elapsed time. An elapsed
time isn't a "time" data type, it is simply a number. For example, the
difference between 2pm and 3pm is 1 hour, not 1 o'clock (1:00). You are then
wanting it formatted as if it was a time.
--
Wayne Morgan
MS Access MVP
"Drum2001" <dr******@gmail .com> wrote in message
news:11******** **************@ v46g2000cwv.goo glegroups.com.. . I have designed a "Time Tracker Database"... Basically a Time Clock. I have report that calculates the number of regular hours worked per week.
Currently I am running the following query:
---------------------------------------------------------------------- SELECT DateFilter.Empl oyee, DateFilter.Time TypeIn, Sum([ClockOut]-[ClockIn]) AS Expr1 FROM DateFilter GROUP BY DateFilter.Empl oyee, DateFilter.Time TypeIn HAVING (((DateFilter.T imeTypeIn)="Reg ular"));
ClockIn & ClockOut are both Time/Date ----------------------------------------------------------------------
Everything calculates correctly up to 24 hours. After that, it messes up. I would like the sum to say something like 38:25:02
Any suggestions would be appreciated!
That works great!! thank you!
Now here is a little twist... How would I make it so anything over 40
hours would be overtime?
Basically how would I code: TotalTime - 40:00:00 = (overtime)
"Drum2001" <dr******@gmail .com> wrote in
news:11******** *************@u 72g2000cwu.goog legroups.com: That works great!! thank you!
Now here is a little twist... How would I make it so anything over 40 hours would be overtime?
Basically how would I code: TotalTime - 40:00:00 = (overtime)
since the total time in seconds is sum(datediff("s s",[clockin],
[clockout]) as given by wayne,
Overtime in seconds is
IIF(sum(datedif f("s",[clockin],[clockout])-40*60*60) >0, sum
(datediff("s",[clockin],[clockout])-40*60*60,0))
and that has to be reformatted as Wayne showed, .
Overtime =
IIF(sum(datedif f("s",[clockin],[clockout])-40*60*60 >0, sum
(datediff("s",[clockin],[clockout])-40*60*60,0)/3600 &
Format((IIF(sum (datediff("s",[clockin],[clockout])-40*60*60 >0,
sum(datediff("s ",[clockin],[clockout])-40*60*60,0) Mod 3600) /
86400, ":nn:ss")
which has to go into the query as a single line, is quite a
mess, and I'm sure I'm missing some () so use the User defined
function below.
overtime = SecToDur(IIF(su m(datediff("s",[clockin],[clockout])-
40*60*60 >0, sum(datediff("s ",[clockin],[clockout])-40*60*60,0))
Public Function sec2dur(seconds As Long) As String
On Error Resume Next
Dim hrs As Long
Dim mins As Integer
Dim secs As Integer
hrs = Int(seconds / 3600)
mins = Int((seconds - (3600 * hrs)) / 60)
secs = seconds - (hrs * 3600 + mins * 60)
sec2dur = Format(hrs, "#,##0") _
& ":" & Format(mins, "00") & ":" _
& Format(secs, "00")
End Function
--
Bob Quintal
PA is y I've altered my email address.
What do I put where? If I put:
overtime = SecToDur(IIF(su m(datediff("s",[clockin],[clockout])-
40*60*60 >0, sum(datediff("s ",[clockin],[clockout])-40*60*60,0))
In the query, it says "The Expression you entered has a function
containing the wrong number of arguments."
"Drum2001" <dr******@gmail .com> wrote in
news:11******** **************@ i39g2000cwa.goo glegroups.com: What do I put where? If I put:
overtime = SecToDur(IIF(su m(datediff("s",[clockin],
[clockout])- 40*60*60 >0, sum(datediff("s ",[clockin],[clockout])-40*60*
60,0)) In the query, it says "The Expression you entered has a
function containing the wrong number of arguments."
I parsed out your code, you are missing a parenthesis somewhere.
overtime =
SecToDur(
IIF(
sum(
datediff(
"s",[clockin],[clockout]
)
- 40*60*60 >0,
sum(
datediff(
"s",[clockin],[clockout]
)-40*60*60,0
)
)
1) in a query, you need a colon to define the column, instead of
an equals,
2) the nesting of parentheses can be a royal PITA. I oftem build
in notepad, tabbing in for each ( and out per ), to make sure
they're in the right places.
Overtime:
sectodur(
IIF(
sum(
datediff(
"s",[clockin],[clockout]
)
)-40*60*60 >0
,
sum(
datediff(
"s",[clockin],[clockout]
)
)-40*60-60
,
0
)
)
With this level of nested calls, I'd probably move the code to a
function.
--
Bob Quintal
PA is y I've altered my email address.
Thank you for your help again!
It looks like were closer. It seems as if it is subtracting 41 mins,
not 40 hours. Any suggestions?
With sample times entered, the queries come up with the following
results...
T
he Total Regular time = 51:59:31
The Total Overtime = 51:18:31
"Drum2001" <dr******@gmail .com> wrote in
news:11******** **************@ p10g2000cwp.goo glegroups.com: Thank you for your help again!
It looks like were closer. It seems as if it is subtracting 41 mins, not 40 hours. Any suggestions?
With sample times entered, the queries come up with the following results... T he Total Regular time = 51:59:31 The Total Overtime = 51:18:31
found this in the code I posted: )-40*60-60
which would give the exact 41 minutes instead of 40 hours error
you mentioned. It follows the second datediff. )-40*60*60 will
give the correct result. So sorry I can't type.
My employer keeps me on staff for my mental acuity, not my
physical dexterity.
--
Bob Quintal
PA is y I've altered my email address.
Bob,
Thank you very much for all your assistance!
That did work!
Bob Quintal wrote: "Drum2001" <dr******@gmail .com> wrote in news:11******** **************@ p10g2000cwp.goo glegroups.com:
Thank you for your help again!
It looks like were closer. It seems as if it is subtracting 41 mins, not 40 hours. Any suggestions?
With sample times entered, the queries come up with the following results... T he Total Regular time = 51:59:31 The Total Overtime = 51:18:31 found this in the code I posted: )-40*60-60 which would give the exact 41 minutes instead of 40 hours error you mentioned. It follows the second datediff. )-40*60*60 will give the correct result. So sorry I can't type.
My employer keeps me on staff for my mental acuity, not my physical dexterity.
-- Bob Quintal
PA is y I've altered my email address.
Is there anyway to make it like the following. If there is overtime,
then have Regular Time display = 80:00:00
If under 80:00:00, then display the correct hours
Drum2001 wrote: Bob,
Thank you very much for all your assistance!
That did work!
Bob Quintal wrote: "Drum2001" <dr******@gmail .com> wrote in news:11******** **************@ p10g2000cwp.goo glegroups.com:
Thank you for your help again!
It looks like were closer. It seems as if it is subtracting 41 mins, not 40 hours. Any suggestions?
With sample times entered, the queries come up with the following results... T he Total Regular time = 51:59:31 The Total Overtime = 51:18:31 found this in the code I posted: )-40*60-60 which would give the exact 41 minutes instead of 40 hours error you mentioned. It follows the second datediff. )-40*60*60 will give the correct result. So sorry I can't type.
My employer keeps me on staff for my mental acuity, not my physical dexterity.
-- Bob Quintal
PA is y I've altered my email address. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Kenneth |
last post by:
Hello,
I'm having some serious problems debugging a script that I'm trying to make
work. I'm working on a form where a user can type in a time (in the format
of HH:MM), and another script automatically calculate how much time is
inbetween.
That part of it is working fine, but what ISN'T working fine is the script
that validates whether...
|
by: flupke |
last post by:
Is there an easy to convert following hour notation hh:mm
to decimals?
For instance 2 hours and 30 minutes as 2:30 to 2,50
I don't really know where to search for this kind of conversion.
Thanks,
Benedict
|
by: Alan Webb |
last post by:
Guys,
I get this:
Regular Hours are any hours less than the number of hours that can be worked
before the hours begin to be counted as overtime in the period.
Overtime Hours are any hours more than the number of hours that can be
worked as regular hours in the period.
Doubletime is similar to overtime but at a higher limit.
|
by: smatthews |
last post by:
Access 2000 question
Hi any help or advise required if possible
i am trying to create a formula for a field. here is what i have
currently
rem:
IIf(Time()<!,MinToTime((*60)),MinToTime(((*60))-(!*60)-(IIf(Time()>"09:00:00"
And
|
by: Drum2001 |
last post by:
I have a table that tracks employee times. I have a column
(Date/Time).
Users, through a form, enter how long it takes them to complete a task.
For example, 03:45 = 3 hours and 45 mins.
I am running the following query:
SELECT Sum(Nz(.,0))
AS SumOfNumberOfCompletions,
Format(Sum(Nz(.,0)),"Short
| |
by: nono909 |
last post by:
I wrote the following time class for the following assignment.i need help in completing this program pleasee.
Write a class to hold time. Time is the hour, minute and seconds. Write a constructor that will allow the user of your class to initialize a time or set the time to all zeros if not initialized. Include all the operators listed above.
...
|
by: ken |
last post by:
Mostly so I can find this again when I need it:
function formatTime(t) {
var myTime = t.value;
var badFormat = "Bad format";
// Ensure we have a value
if (myTime == "") {
return(true);
}
|
by: JamesG |
last post by:
I'm trying to convert a unix timestamp to a readable time, such as 4
hours ago, or 25 minutes ago.
My code is below:
function ago($ts) {
/* time difference */
$ts = (time() - $ts);
if($ts < 60) {
/* <1 minute */
|
by: yasirmturk |
last post by:
Standard Date and Time Functions
The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.
They are simple, easy, and brief and you should use them any time you need to incorporate any date literals or...
|
by: RN1 |
last post by:
A Form has 2 TextBoxes where in users enter date & time. Example (in
mm/dd/yyyy format):
09/20/2008 17:54 (1st TextBox)
09/29/2008 6:13 (2nd TextBox)
How do I find out how much time (in hours & minutes) has elapsed
between the 2 datetime values? I need the exact time difference.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |