I have a tracking database I have been working on and so close of being done just held up on this last part. What I am trying to do is have the totals = Ex. 1.25 + 3.50 would give Grand total of 5.15 not 4.75. The table has a field called time spent which stores the time. I have attached an example database. So whenever the minutes = more then 60 for it to add 1 to the hour.
View RepliesBasically I have a table setup where Time Fields are stored. What I need to do is in a Query, have the difference of two Time Fields calculated, and then displayed on a Report. Here's what I have so far...
Table
- Start Time
- End Time
Query
Difference Time: DateDiff("n",[Start Time],[End Time])
All that works fine and shows up on the report, except for the fact that the time is showed in straight minutes, so for a difference of 1:30 minutes, it just shows 90 minutes. I need to make it so that on the report in the Difference Time Text Box, it's showed as 1:30 instead of 90.
I tried putting the following in the Control Source for the Difference Time Text Box on the report, but it returns an error.
=[Difference Time] 60 & Format([Difference Time] Mod 60, ":00")
I have a query that uses DateDiff to give me Minutes of an in and out time.Online I found the following to use in a textbox control source on a report to convert the minutes to Hours:Minutes.
=[Minutes] 60 & Format([Minutes] Mod 60, ":00")
But when I enter this it gives me an error. I am not sure how to fix it. I tried writing it like this with RegHours being the source of that column
=[RegHours]60& Format([RegHours] mod 60, ":00")
but when I go back to report view that text box then says #Type!
How can I convert hours and minutes to minutes as an Integer?
For example, convert 2:15 to 135.
Any help is greatly appreciated.
I would like to calculate the time difference between two dates, what ive got so far is
TimeSpent: DateDiff("n",[datein],[dateout])
This calculates the minutes between two dates, Can you format this so that it calculates hours. minutes for example 124 minutes would be 2.04?
I thought using "h/n" might work but it hasnt
thanks
Hello everyone,
I am new to this forum, this is my first time. I hope someone can help me with my problem. I am creating a small database using Access at my work place. I came forward with 2 problems.
1. I need to create a field (Down Time) in a form that can store hours and minutes like 40:30. If I format the field as Short Date it wont allow me value higher than 23:59. Also, let you know that I am using the field (Down Time) in a calculation.i.e. Run Time - Down Time = Actual Time.
2.I also need a help on for the following calculation.
Unit per Minute =Quantity(number field)/Actual Time.
Your help is greatly appriciated.
Hi,
I am trying to show the time difference between 2 times but the calculation is not working correctly.
First I work out the totaltime on mins between 2 dates using datediff, then I am trying to convert to hours and minutes like :
Total_Time: Format([Sumoftotaltime_mins]/60,"00") & ":" & Format([SumofTotaltime_mins] Mod 60,"00")
This is working until I have something like :
03:00
15:45
25:20
this should work out as 44 hours and 5 mins but for some reason it is showing as 43 hours and 5 mins.
why would this be ?
I have a table wherein the time worked by 25 employees are recorded. This table has the start time, endtime, break time and late times. The Late Time is the number of minutes that the employee is late to work. I am required to do a query of the team's (all 25 staff) late times per month. I have done a query which shows the late times of the individual on the dates that they were late to work and created a crosstab query for that.
I am going around in circles. How can I have a total of the team's late times in a query? Please, could someone please point me in the right direction?
Hi,
I have a bit of a problem. I am making a program in access 2002. In my program you should punch in hours:minutes, this is for keeping track of how long a job has taken to perform. The problem is that it could exceed 23:59, it could take up to a week or even more. In my database we have already put in a lot of info where it doesn't exceed the 23:59, I would like to keep this posts and just to put in new ones that I could use further on when I am about to take out reports, where I have to calculate time. Is there someone who knows what to do ??
Regards
Greger
For Example: I have a field, let's say called Hours set to Data Type Number and Double format.
The user should only be able to enter time in 1/4 hr increments.
1 hr or 1.25 or 1.5 etc...
I have time sheets from the past I need to enter but have found that some years the time punch minutes are given as if an hour equals 100 minutes not 60. Other years the minutes are given correctly. How do I format this field so that if I type 3.75 it will convert to 3.45 hours? Will I have to add another field to enter this and then convert to my field where hours and minutes are correctly entered? :confused:
View 2 Replies View RelatedI have a table of timecards each recording minutes spent on each task for each client and I have summed those minutes by client in a query.I have then in the same query converted each sum of minutes to hrs:mins format using this calculated field:
HrsMins: [SumOfMinTime]60 & Format([SumOfMinTime] Mod 60,":00")
So this turns e.g. 261 minutes into 4:21..Based on that query I have a report showing total hrs:mins spent on each client, e.g.
Smith 4:21
Jones 5:32
James 1:23
Now I want to show a total at the bottom, i.e. in the above example it would be 11:16.So for the control source for a total field on the report I tried:
=Sum([HrsMins])
But when I do that I get an error message: "Data type mismatch in critieria expression"I may be because I am trying to sum calculated fields amounts that are calculated using a function.Or it may be that the format is not being recognised as hours and minutes and thus cannot be added up.way in which I could get a total here in hours and minutes format (hrs:mins).
I am using the calender control 11.0. By this calender I can only set the date year, month and day. I can not set the clock time:Hours, minutes and seconds. Is there a calender that enables me to set the date in the format:dd-mm-yyyy HH:MM:SS, means day,month,year Hour:Minutes:Seconds.
Grateful
I'm working on a utilization database and pulling Excel data from Cognos 8. I use Access formulas to separate hours and minutes because I want to use time (.30, .45 (minutes)) instead of quarterly increments (.25, .50). I've successfully segregated hours, because I also have to turn days into hours. When I try to calculate out a column just for minutes, my formula keeps the number of days and I don't know how to fix it so the column just shows the minutes.
An example is a job has a decimal time of 2.02083333, which when done correctly is 48 hours and 30 minutes. I get the 48 hours correct, but the minutes shows as 2.30 instead of 0.30. That inflates the total job time to 50.30. This is the calculation currently in my database to pull out minutes:
Minutes: Int(([Min]60)24) & "." & Format([Min] Mod 60,"00").
How do I get rid of the '2' in the 2.30?
Access 2010
I want to subtract 8 hours of work of the Result of employee work hours/
There is attachment...
I read somewhere that I could right click on a form that I designed and convert it to a report. However, I have yet to figure that out. I don't have that menu option when I right click on it. Is there another way to do this so that I don't have to duplicate effort here?
View 8 Replies View RelatedI have a date field in a form which is not compulsory.
I then pull this date field into a query to create a report.
If the date is not entered then is it possible for "Date not entered" to be written into the report automatically. I have tried playing around with nulls but have had no luck.
I have a check box in a form (set to yes/no in the table) and when I generate a report I would like the check box value to show up as yes or no in text box, but I can't seem to figure it out.
View 3 Replies View RelatedIn a table called Appointments and the field called Plastics we enter the initials of surgeons.
However in a report i want the initials converted to full text - in the example below the initials are HJC
The expression below doesn't work - it just returns everything in the Plastics column.
Any ideas?
Expr1: IIf(HJC([tblAppointment]![Plastics]),"Heather Cleland")
Hello all,
This forum has been a lifesaver in the past and I hope someone can help me now.
I have a Contacts form that is used to input all the client's information. (name, date of birth, address etc).
I want to be able to force the person entering the data to not be allowed to tab to the 2nd text box (or any other text box on the page) unless in the first text box data has been entered (the first text box is client name)
However, I also need to be able to overide this if the person entering the data clicks on a "Cancel" button (another button on the page which closes the form).
Is this possible and if it is any help would be greatly appreciated!
Thank you in advance,
Rob
I have a query that shows a list from GPS software (car fleet management), unfortunately the software is not capable of doing a decent reports thus I'm trying to use its data and do it myself.
The table structure is as follow:
Car, Date, Time, Position.
The time interval I have now is 1 minute (short time format), I don't need it so detailed, that's why I was thinking to limit this into 5 minutes interval.
Is it possible?
Thanks for any suggestions.
Here's one that I'm having difficulty on. I know just enough about code to completely confuse myself, so if you answer this, please explain in detail. I have a training database, in which a portion of it stores the scores of events of a physical fitness test. Part of this test is a times 3 mile run. I need to calculate a point value for the submitted time, but here's my dilema...If I use the time/date feature it accepts the input as hh:mm, and will not let a time of more than 24 minutes be entered, since it sees this as 2400, or midnight in military time. I need the data to be inputed as mm:ss, and I have another text box that accepts this info and converts it to a point value. I would appreciate any help you could provide. Thanks in advance.
Scott
Hi,
If I have a time like 22:00 and I want to add lets say 300 minutes to it; so it now shows 03:00 how do I do this? I can do it in Excel alright with the TIME function but cannot find a similar feature in Access
Liam
Can anyone tell me if there is a way of displaying a number, which represents the number of minutes of elapsed time, into the format days:hours:minutes elsapsed?
For instance the number (in minutes) 1530 would display as = 01:01:30. 1 day, 1 hour & 30 minutes of elapsed time.
Any help would be appreciated.
Paul.
I am truly stumped. I have a query that sometimes takes less than 1 second to run, and sometimes takes close to 5 minutes. I am making NO changes to the query. I first thought it was a network issue, so I moved the whole db, both front-end and back-end, offline. No change. So I thought maybe it was a linked table issue, so I moved all the tables used by the query to the front end. No change. Every once in a while, the query runs at lightning speed, less than 1 second. Most of the time, it takes 3-4 minutes, or longer.
Here's the SQL:
SELECT PCAInternalPareto1a.row, PCAInternalPareto1a.column, PCAInternalPareto1a.Noun, PCAInternalPareto1a.Rev, PCAInternalPareto1a.InspPoint, PCAInternalPareto1a.cell, PCAInternalPareto1a.DefectType, CLng(nz([firstofTruncatedOpSeq],0)) AS ZOpseq, Sum(PCAInternalPareto1a.DefectQty) AS SumOfDefectQty, PCAInternalPareto1a.poo, PCAInternalPareto1a.repcode
FROM PCAInternalPareto1a LEFT JOIN BOMOutRefDesOnly ON (PCAInternalPareto1a.RefDes = BOMOutRefDesOnly.RefDesOnly) AND (PCAInternalPareto1a.partno = BOMOutRefDesOnly.PCAItemNo)
WHERE (((nz([FirstOfComponentItemNo],0)) Like "*"))
GROUP BY PCAInternalPareto1a.row, PCAInternalPareto1a.column, PCAInternalPareto1a.Noun, PCAInternalPareto1a.Rev, PCAInternalPareto1a.InspPoint, PCAInternalPareto1a.cell, PCAInternalPareto1a.DefectType, CLng(nz([firstofTruncatedOpSeq],0)), PCAInternalPareto1a.poo, PCAInternalPareto1a.repcode;
PCAINternalPareto1a is itself a query, which always runs fast. BOMOutRefDesOnly is a table with about 84,000 records. I have indexes on the joined fields.
I know the where statement looks useless but I have my reasons for doing it this way. I tried deleting the where statement and it ran really fast. Then I re-added it, and it still ran really fast. THen slow again. I can't find any relationship between anything I've tried and the speed of the query!!!! What's going on??????
Access 2002
I have some machine data to display showing running speeds. This is logged every minute when the machine is running but not when stopped.
how do I display a bar graph with a timescale along the bottom of minutes showing gaps when the mchine is down. :confused: