Hello,
I am a new member and am glad to a part of this forum.
I am facing a slight problem while doing time calculations in MS access. I ve prepared a time sheet for my workers. Their regular time is till 17:00 hrs and anything above that is overtime. I am using the formula DateDiff("n",[Regular Time],[Time Out])/60. I am able to calculate the overtime if the worker finishes his work before the day ends. However , if he works untill for eg 2:00 AM , (i.e 9 hrs of over time) , i get a negative result.
Can this be worked out .
Thanks much in advance
16  3159  NeoPa 32,579
Recognized Expert Moderator MVP
You have (accidentally) posted this question in the Access Articles section. This is NOT an article.
I'm moving this to the main Access questions forum.
MODERATOR.
NeoPa 32,579
Recognized Expert Moderator MVP
Hello,
I am a new member and am glad to a part of this forum.
I am facing a slight problem while doing time calculations in MS access. I ve prepared a time sheet for my workers. Their regular time is till 17:00 hrs and anything above that is overtime. I am using the formula DateDiff("n",[Regular Time],[Time Out])/60. I am able to calculate the overtime if the worker finishes his work before the day ends. However , if he works untill for eg 2:00 AM , (i.e 9 hrs of over time) , i get a negative result.
Can this be worked out .
Thanks much in advance
Use the Mod operator. This will require ensuring that the value is an integer when processed but shouldn't be too much of a problem.
What are the units you're working in (IE. What does a value of 1 mean)?
Try changing your Time fields to Date&Time fields, such as by using the Now() function for logging the time in and time out.
Date/Time functions can be tricky because of the way Access stores and calculates time, but this should actually be a simple way to calculate the difference you are looking for.
Regards,
Scott
You have (accidentally) posted this question in the Access Articles section. This is NOT an article.
I'm moving this to the main Access questions forum.
MODERATOR.
Sorry , i am new to this . I'll keep this in mind henceforth.
Thanks
Sorry, Adrian! Must have been posting at the same time again...
Regards,
Scott
Try changing your Time fields to Date&Time fields, such as by using the Now() function for logging the time in and time out.
Date/Time functions can be tricky because of the way Access stores and calculates time, but this should actually be a simple way to calculate the difference you are looking for.
Regards,
Scott
For the time in and time out , the data type is set as Date/Time , however the result that i get is an integer , so that it calculates the exact overtime hrs.
U mentioned about the Now() function; is that VB . I ve no prior knowledge of VB. All i am using is tables and queries to do the above.
NeoPa 32,579
Recognized Expert Moderator MVP
Sorry, Adrian! Must have been posting at the same time again...
Regards,
Scott
Don't be daft Scott! You have every right to post your ideas in here whether I choose to or not :)
That reminds me I need to get your "Expert" invite off to you as soon as. I'll get it out today if humanly possible :)
NeoPa 32,579
Recognized Expert Moderator MVP
shreyansghia,
Have you looked at post #3?
If so, can you provide answers please.
An alternative approach is to say : - If datStart > datEnd then datEnd = datEnd + 1
where Date/Time fields store days as 1.
You then continue as before.
Of course, this can only be a problem if you don't store the Date/Time values fully in the first place :/
Thanks again, Adrian!
If you put this:
in the default value of your Regular Time field at the table level, it will automatically enter the current Date (8/24/2007, according to my computer) and the normal quitting time of 17:00 hours.
Depending on how you enter your Time Out data (i.e. manually through a text box, or by clicking a command button, etc.) you can add in the appropriate place
You shouldn't have to change much more than that, as your fields are already being compared, and the calculation as you have it of the date/time should work out fine.
Have to run, and won't be back until this aft... Post more questions as needed, and someone will help :-)
Regards,
Scott
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Jerome |
last post by:
Hi, I'm a bit confused ... when would I rather write an database
application using MS Access and Visual Basic and when (and why) would I
rather write it using Visual Studio .Net?
Is it as easy in Visual Studio to create reports and labels as it's in
Access?`
The advantage of VS.net is that not every user needs Access, right? And
that would eliminate the Access version problem as well I guess.
|
by: lduperval |
last post by:
Hi,
I`m trying to do date calculations in three types of time zones: local,
GMT and specified. The issue I am facing is that I need to be able to
specify a date in the proper time zone, and I`m having a heck of a time
doing so.
I have created a form where I use drop downs do specify year, month,
date, hour, minute and seconds. When the form is loaded, the dropdowns
have to display the proper values for the current time zone type. This
|
by: bluerocket |
last post by:
I have searched this group, and am not finding the answer I am looking
for -- hope you can help.
I have a front-end MS Access database hooked via a MyODBC link to a
MySQL database.
A modified date is going into the database.
Need to be able to perform calculations on the dates in order for
newly added notes to show up in bold on a report.
|
by: MacDermott |
last post by:
I have an Access MDB which instantiates a class in a custom DLL, manipulates
it for a while, then sets it equal nothing.
The MDB does other things,too, and generally behaves itself as desired.
But, if I have done the DLL thing, even though I close the MDB, Access
refuses to close.
If I haven't used the DLL, all is well.
The DLL is one I wrote myself in VB.
I'm quite stumped, and rather guess the problem may be in the DLL (I've also
|
by: Yannick Turgeon |
last post by:
Hello,
We are in the process of examining our current main application. We have to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The application is relatively big: around 200
tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data
(SQL Server 2000), 40 users.
I'm wondering what are the disadvantages of using Access as front-end? Other
that it's not...
| | |
by: migueltxa |
last post by:
Hi all,
I have a problem with some calculations in MS Access 2003. I have 3
fields (Qty, Price and VolumeUSD) in a form and the following formula:
VolumeUSD = Qty * Price.
Qty and VolumeUSD are formated as "Standard" and Price as "General
Number".
Example: Qty = 4,400 ; Price = 39.1602
|
by: MrsBean |
last post by:
I've been working on learning how to use PHP with MS Access, and as
long as I use ODBC, I can make it work. I want to use a DSNless
connection. Everthing I have tried has failed. Can anyone show me a
simple example?
MrsBean
|
by: Michael Bialowas |
last post by:
Hello all, I have been searching the net endlessly and found this ng, so I
thought I would give a try. Anyways, I have a few problems I am trying to
implement a combo box which presently contains the following: Description,
Price, and Treatment code. I wish to do the following: when the description
field(combo box) is clicked I want the price, treatment code, insured field
to become automated(update the corresponding data automatically)....
|
by: Wiline |
last post by:
Hi,
I'm new at using Microsoft Access (2000), and I've been working on a relatively simple scientific database. I've created a "MakeTable" query, which has worked well in the past and I update the table regularly as I enter more data. All the query does is run some calculations from data entered previously, and it puts the results of those calculations in this new table.
The last time I tried to update this table by running the query, I...
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
