I have a query that calculates elapsed time from TimeIn and TimeOut formatted to hours and minutes. I need be able to mulitply the elapsed time by the hourly wage in order to find the hourly pay.
How do you format the time so that it is compatible to multiply with the wage?
My SQL statement is below. Thanks for your help.
SELECT tTimeCards.ValetFirstName, tTimeCards.ValetLastName, Format([TimeOut]-[TimeIn],"hh:mm") AS HoursWorked, [HoursWorked]*[tValets]![HourlyWage] AS HourlyPay
FROM tValets INNER JOIN tTimeCards ON tValets.ValetID = tTimeCards.ValetID;
Formatting issue regarding elapsed time calculated using DateDiff().
I understand that you can specify the output value for DateDiff(). In my case I have chosen "n" for minutes. Each result in my query shows the correct calculation in terms of minutes.
[PunchIn] = 11/23/2013 8:11:28 AM [PunchOut] = 11/23/2013 5:43:30 PM
Now when I try to format the result in terms of H:MM (be it in a form or a report) I get varied results. I'll illustrate an example below:
=Format(([ShiftLength]/60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 10.32 Not correct
=Format(([ShiftLength]60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 9.32 This is correct but I need my result to be in the form of a decimal such as my next example
=([ShiftLength]/60) Returns 9.53333333. Getting there but how do I have this result only show two decimal points 9.53?
I have a working dB which can calculate a shift duration and sum total all shifts worked within a period for the purpose of producing a labor report for payroll. I have successfully used the DateDiff function and converted the minutes to HH:MM on my form and reports. Now I want to calculate elapsed time for a specific period within a shift, I'll call it OtherHours and I am aiming to calculate a portion of time that meet the following conditions below. I am using field names of [PunchIn] and [PunchOut] and both are of type General Date.
IF [PunchOut] ISNOT Saturday,Sunday EXIT FUNCTION ELSE IF [PunchOut] ISNOT Between Midnight and 0559 hours EXIT FUNCTION ELSE DATEDIFF ("n", <MIDNIGHT>, [PunchOut])
My thoughts are to solve the DateDiff portion and then figure out how to apply the conditions within the IF statements.
I am making a database to log working hours onto for different contracts I work on.
The fields I am having a problem with are "start date and time", "End date and time" and then "Elapsed time"
I need to populate these fields in the format 13/08/2006 17:20 etc.
How do I set the date/time format to do this and then how do I calculate the elapsed time in days,hours and minutes ? (perhaps just hours might do instead of days and hours) I also need to take out non working time ie 6pm through to 8am to make the calculations correct.
Everything else works fine bu I am stuck on this now.
I am trying to build a form to calculate time elapsed/time difference. Example if one turns up for work at 1900hrs (start) and ends his duty at 0200hrs the next day (end), how much time has elapsed? I am trying to use this argument : Elapsed: IIf([end] > [start], Format([end] - [start], "Short Time"), Format(1 - [start] + [end], "Short Time")) Unfortunately, I am not grtting the desired result. Could anyone please assist? Thanks
I have been trying to help a user calculate elapsed time. I referenced the GetElapsedTime from the following Microsoft article
http://support.microsoft.com/kb/210604/
It displays ok on a form or inserting on the detail in a report like it suggests but the user would also like to total up the elapsed time on the report. Not sure how to do that with the way this function is built.
I have attached a really simple db where in a query I'm trying to determine the elapsed time between A sent date and a received date. Can some tell me what I'm doing wrong? Thanks..
I have nearly achieved this from an example on the Microsoft site but am having problems with the subforms. Can anyone take a look and if possible provide me with guidance as to what I am doing wrong. The instructions are included.Any help with this would be very much appreciated. Kind regards. Bernard
I am creating query that calculates time interval so that this is used to calculate pay. Employees clock in at [time_in] and clock out at [time_out]. Sometimes time_out is on the next day of time_in.So if is substract time_out - time_in I will get a negative number.I am therefore using the following formula to extract the time interval; Interval: Format([time_out]-[time_in],"Short Time")
this gives out a result such as 3:30 when in fact I would need 3.5 to calculate pay by multiplying with rate.How do I convert 3:30 to 3.5. From the searches I get that I need to multiple 3:30 by 24. But when in put done in the query Interval: Format([time_out]-[time_in],"Short Time")*24
I am currently in the process of creating an Employee Database. I have a 'staff information' table with several fields, one of which is a 'contracted hours field'. I have a spreadsheet linked to this database and therefore require this field to be formatted correctly in hours and minutes. For example...I wish to store a decimal time of 37.5 as 37:30.
I have a database with two fields [RECEIVED] and [DISPATCHED] which unfortunately are text fields which I can't change (linked via ODBC). Time is entered in these fields as military time.
I created a query with [DISPATCHED]-[RECEIVED] which works unless the time spans different hours. Example 1605-1555 returns 50 instead of the correct 10.
Any idea how I can use a query to calculate this accurately. I am an intermediate user. Any help appreciated.
class status changeby changedate statustracking INCIDENT-RESOLVED- Person A-2/20/2014 9:05 - INCIDENT-RESOLVED-Person b-2/20/2014 11:57 - INCIDENT-QUEUED-Person b-2/19/2014 13:57 - 01:24:08 INCIDENT-QUEUED-Person c-2/19/2014 15:21 - 19:29:58 INCIDENT-QUEUED-Person c-2/20/2014 10:51 - 01:06:00
I need to be able to add the statustracking time together per person per status (so person c would need 19:29:58 + 01:06:00 added together). Access keeps trying to add dates to the field or reconfigure the numbers to date ime or zeros if I use any of the data type other then Memo or text.
I have a large table with a number of fields. I have written the query that I want but can't quite work out how to get it to do the main thing I need. I have several fields that I need to generate in the report but what I want it based on is the field called Date Approved.
When the Date Approved has been Active for more than 4 weeks I want to pick it up in the report. I don't want to set a date to start from but pick up everything that has passed 4 weeks in "Date Approved".
I want to calculate total hours and mins by adding a load of times (in time format) on a report. I've got the total number of minutes and converted this to hours : [txtmins]/60
Now I want to get the remaining minutes by using : [totalmins] - ([totalhours]*60)
However, how do I round DOWN the [totalhours] before it goes into the above sum. I can round it, but it always seems to round up.
how to change default currency in Access 2013 to a "foreign currency" (i.e. to Indonesia Rupiahs) without changing control panel (Region/Language>currency) - now setup as USA ($US)?
I've searched and searched to no avail to find the answer, hopefully you can help.
The story is:
At work, our holidays are now taken as hours off. For example...if I take a Monday off, its 8hrs and 5 min and a Monday morning would be 4hrs and 5 min...What I want to do is calculate how many hours I have taken off throughout the year.
The way I was going to do it was:
Create Table called Times,
Fields = Week, Day, Morning, Afternoon, Friday and 1hour all Data Type "Date/Time"
Similar field names Wk, Days, Morn etc. They would be Data Type "Number"
Now in my simple head, I thought I could simply record how many weeks or days off i had off. Then create a query (for example) Week*Wk to calculate the amount of hours I had off. In simple terms if I had 2 weeks and 2 days off, the query would multiply Week (37) by Wk (2) and Day (8:05) by Days (2), giving a total of 90 hrs and 10 min.
I know there is a datatype mismatch, but is there a way round this.
hey, i would like to see in the room price column, the total price payed for the room judging by the loyalty(no of nights). So basically i want the loyalty(no of nights X roomprice. At the moment, when i run the query it in the room price it shows just the one night price, but i want to X that by how many nights the person has stayed. I have tryed putting the sum function in the roomprice field and it didnt work.
Hi all, I have a table shown in a form, beside that table a textbox with a button. How can I multiply the numer entered in that textbox by the the number displayed in the table, and view the result in the same table?? Please help me as soon as possible?
Thanks in advanced and sorry for bothering you.....
I have a table called tbl_IE which has a field named frequency.
I want to multiple all of the values in frequency where the ID is 1. I want to display this on the form as the data is inputted and also on the final report.
For each Name, Number1 will always contain the values from 1 to 5. Number2 can vary, however.
I need constructing a query that, for each name, will look at the values in the Number2 column when Number1 is 1 or 2 and multiply them. So, the result I'm looking for is:
Name1 2 Name2 10
(Since 2 = 2 x 1 and 10 = 2 x 5)
I haven't had much look Googling (most search results involve multiplying two columns together, which isn't exactly what I'm after).
I'm trying to create a query that will return a calculated field called "PnL".The formula for "PnL" is as follows: PnL = notional*management_fee*NAV*
The "notional" field is a value derived from a table called "Deal_information" (primary key deal_id) and has an associated "management_fee" and "product" field. So obviously finding the product of the notional field and the management_fee field isn't a problem.
However "NAV" field is from another table (VL_information) that contains the historical Net Asset Value of each product. Fields are (VL_id, vl_date, product, NAV).
Therefore for each "notional" record I would like to return the historical PnL.By way of example. Lets say that the notional valuefor a particular record is $100, with a corresponding management fee of 10% (the managment fee is product dependent). Then I would like to find the evolution of PnL by multiplying this record by the evolution of the NAV. This would do so for each notional value.
I created a form for 'LabIssue' in which data will be entered. In the form I want to calculate the 'ChemicalAmount' by multiplying 'Store.Costperunit' with the 'QuantityIssued' which will be entered by the user. ChemicalAmount and QuantityIssued will be stored in 'LabIssue' table. I tried it through the expression builder but im getting #Name? error, did alot of things but couldnt figured out whats the problem.
I am attaching the initial database also for reference.
i am fairly new to the use of VB so (1) I am not sure if this is possible and (2) i wouldn't know where to start really so any help is greatly appreciated. I guess that in order to achieve whet i eventually want to (assuming it is possible that is) will be based on using some element of DateDiff. I have searched the forum and whilst i now have a basic understanding of the DateDiff function, i could find nothing that would help solve my problem.
On my form - which is a continuous Form displaying a number of the same type of records - i have a number of fields including InvoiceDate and TaxInvoiceDate. What i have been trying to achieve is the following:
1. Should the number of days since the InvoiceDate and the current date exceeds a certain i would like to format the InvoiceDate field, for example, if more than 30 days but less then 45 days have elapsed then the field could be this and if greater than 45 days then this. This indicates overdue accounts, with me so far? This i have managed to implement this using Conditional Formatting.
2. However, if a date is included in the Tax Invoice Date (meaning that the account has been settled / paid) i want to return / leave the text normal colour / font. This second point i guess will not be achievable when using conditional Formatting?
So, i believe that in order to achieve both points i need to use VB? If so, can anybody help me by suggesting suitable coding and where on the form / fields this code should go.
(Grovel Time) I should add that it only recently that i started using Access and developing my own databases but i have learn't so much from this forum - who knows sometime in the future i may also be able to partake by answering queries myself;)
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
