Working With Duration.....

Mar 23, 2005

I have an import source that gives me lots of fields containing duration in format hh:mm:ss

This is a comma delimited txt file with headings and imports just fine, but I (thought) that i had to import the duration fields as text types....

Now, what can i do with that? I need to calculate (sum, average) on these fields, but can't. It seems a bit daunting (and stupid) to try and split these into separate hour, min and sec fields and use TimeSerial to put them back together. Do i have other choices?

It is not too late to import as a different format but here is a couple of lines of the source file:

Login ID,Date,ACD Calls,Avg ACD Time,Avg ACW Time,Extn In Calls,Avg Extn In Time,Extn Out Calls,Avg Extn Out Time,ACD Time,ACW Time,Agent Ring Time,Other Time,AUX Time,Avail Time,Staffed Time,PC Agent Occupancy w ACW,PC Agent Occupancy wo ACW
OrderCalls,01/01/2005,0,0,0,0,0,0,0,:00:00,:00:00,:00:00,:00:00,24: 00:00,:00:00,24:00:00,0,0

as you can see, leading zeroes are dropped.....

please help!

View Replies


ADVERTISEMENT

Duration Cut Off

Jul 10, 2007

Hi to all,

I have a table of daily_outs with two columns date_out and date_in and below are my sample:
Table:daily_outs
line________date_out______________date_in_________ ___ duration(minutes)
1______#12/25/2006 12:26 PM#___#12/27/2006 13:26 PM#_____2,940.03
2______#12/26/2006 04:33 AM#___#12/27/2006 12:26 PM#_____1,913.00
3______#12/26/2006 01:16 AM#___#12/26/2006 15:25 PM#______849.03
4______#12/25/2006 01:05 AM#___#12/28/2006 15:27 PM#_____5,182.22

I would like to query the date_out=12/26/2006 00:00 AM and date_in=12/26/2006 11:59 PM. Below are the result I would like.

line________date_out______________date_in_________ ___ duration(minutes)
1______#12/26/2006 00:00 AM#___#12/26/2006 23:59 PM#_____1,439.98
2______#12/26/2006 04:33 AM#___#12/26/2006 23:59 PM#_____1,166.00
3______#12/26/2006 01:16 AM#___#12/26/2006 15:25 PM#______849.03
4______#12/26/2006 00:00 AM#___#12/26/2006 23:59 PM#_____1,439.98

Can anyone help me on this?

Thanks,
Corbitext

View 8 Replies View Related

Calculating According To Duration

Jun 26, 2005

I have a form with three fields:

CallStart
CallEnd
Cost

When a call is logged, I have it set that the time is entered automatically viz =now(). I would like it where if the cost = £20.00 the call end would be automatucally 30mins ahead. If the cost = £40.00 then the call end would be 1 hour ahead. And if the call costs £10.00 then the call end would be 15mins ahead.

Any help would be appreciated.

View 2 Replies View Related

Calculating Duration

Nov 10, 2004

HI! I am a university student taking a computer science course. I have created a database with multiple doctors and patients. Within the patients’ visits table I have included the two fields [Time Entered] and [Time Left] with values entered in these fields, for example, of 2:50:00PM and 3:30:00PM respectively. Within a query I am asked to calculate visit’s duration using MS Access Minute function. The expression I tried to use as a calculated field was:



Duration: (((hour ([Time Left])-hour ([Time Entered]) * 60) + (minute ([Time Left])-minute ([Time Entered])))



However, when I tried to run the query using this expression, an error message displayed which read “The expression you entered is missing a closing parenthesis, bracket (]), or vertical bar (|)”.



A fellow student told me to delete the first parenthesis and run the query again. I did this and the query did run, but returned vales of –820, -802, -455, etc for the duration of each patient’s visit. Can anyone please help me with the correct calculation??????



Marcella

View 2 Replies View Related

Rounding Duration To An Increment Of 15

Mar 13, 2008

Hi all,

I am trying to get the formula to round to 15 seconds to charge rating events.:confused:

so 76 seconds will calculate up to 90 seconds..
& 91 will be 105 seconds.. and so on.

Any help greatly appreciated.

Thanks Dean

View 9 Replies View Related

Count Duration Between Records

Oct 5, 2005

is it possible to count the time between records? example

Table set up:

LogID
StartTime
StopTime
ProblemCode

Example records:

Id -------> StartTime ------> StopTime--------> ProblemCode

1 -------->06/10/05 10:00 -> 06/10/05 10:02 -> A101
2 -------->06/10/05 11:00 -> 06/10/05 11:02 -> A131

i want to count the time elapsed between, ID 1 ending and ID 2 starting, can ido this?

if so how..

the duration i am looking for in this case would be 58 mins

cheers

Andy

View 2 Replies View Related

Duration Field NOT Time

Apr 15, 2012

what is the best way to record durations, not time?I want to have a table that records charging data such as below:

startDate

startTime
Duration
(hh:mm:ss)

15/04/2012
12:00:00
13:00:00

In this example you can see we started charging the device at 12pm on 15/04/2012 for 13 hours, from this I will be working out the endDate/time using dateAdd etc.Do I set the duration field up as a dateTime and if so how do I avoid the DB thinking that the field is a time and not a duration, meaning that the endDate would be 1pm on the 15/04/2012 instead of the correct 1am on the 16/04/2012?

View 6 Replies View Related

How To Calculate Average Time/duration

Jun 20, 2007

I hv a access mdb which contains one column as "duration", data format as hh:mm:ss, e.g. 00:02:20 - 2 mins 20secs.

How could I calulate the average of the duration?

like sum of all duration fields / the ttl no of records.

View 1 Replies View Related

Queries :: Time Duration Over Midnight In Decimal Hours

Dec 22, 2014

I want to calculate decimal hours duration taken for a job starting before and finishing after midnight.

I have something that works but it seems cumbersome.

Hour(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))
+ (Minute(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))/60)

View 11 Replies View Related

Modules & VBA :: Automation - Create Billing Periods For Project Duration

Nov 19, 2013

I developed a finance tracker database for tracking project revenues and costs (forecast and actual amounts). I have a form where we enter a new Work Order in the DB. This Work Order form has a continous subform where we create all the billing periods needed for the life of the Work Order. Once the billing periods are created (opened) we can then add our revenue and cost forecasts for each period. We have a Billing Period lookup table that has our billing periods with their respective start and end dates (which usually begin around the last week of a month and end about 3 weeks into the next month).

For the purpose of this question, lets say we only enter the required WO_Number and WBS_Code (Composite key) and the Work Order Start_Date and End_Date. I want to click a button to runs some code to automatically create all the billing periods for which their start and end dates fall into the Work Order start and end dates.

For example, say a Work Order starts on 5/1/2013 and ends on 7/31/2013

The billing period dates in the Billing Periods lookup table are as follows:
May-13 ---> 4/22/2013 - 5/19/2013
Jun-13 ---> 5/20/2013 - 6/23/2013
Jul-13 ---> 6/24/2013 - 7/21/2013
Aug-13 ---> 7/22/2013 - 8/18/2013

Then we would need the following billing periods created in the Work Order subform:
May-13
Jun-13
Jul-13
Aug-13

I don't even know where to start on this. Is it possible to automate this process with the setup I have? If so, how would I structure the VBA code/logic to use the billing period lookup table and create entries in the subform for all the billing periods that fall into the duration of the Work Order.

The attached DB is a stripped down version with only the tables and forms needed for this problem.

View 2 Replies View Related

Queries :: How To Input Race Times As Duration In Access Database

Aug 30, 2014

I am creating an access database for the results of my triathlon times and I am having trouble with the race results. Based on some information I found here, I am using number fields for the swim, bike and run times because I want to do calculations and also sort them and it doesn't sort properly if they are text.I have created separate fields for the hours, minutes and seconds for each of them.

Now I am trying to make a calculated field to convert the numbers to the following..For a swim time, I want to convert it to the time per 100m. I have the calculation for that, but the result gives me the decimal portion and not the actual seconds portion and I am stuck. Here is what I have so far :

Sw100m: (([SwMin]+([SwSec]/60)+[SwHr]/60)*100/750)

So as you can see I have added up all the number to get the total number of minutes and then converted to the minutes per 100m (the race is 750m). And the result gives me 3.31. But the .31 is a decimal and I want to have seconds which is 18 seconds. I know how to do the calculation on paper but I don't know how to change my formula to fix this. On paper I have to multiply .31 x 60. But how do I refer to the decimal portion of the number and modify my formula? how to input race times as duration in an access database.

View 11 Replies View Related

Queries :: Breaking Out (Timestamp And Total Duration) Into 15 Minute Interval?

Dec 20, 2013

I am trying to figure out how to take a table of timestamps and activity duration and break it into intervals, where it groups the duration into the time spent in each 15 minute interval. I have included a sample of the data and output. Is there any way to pull this in Access (or SQL)?

Raw Data
EmployeeActivityTimeStampStartTimeStampEndTotalDuration
Doe,JohnSomeActivity12/16/13 9:06:02 AM12/16/13 9:57:14 AM0:51:12
Smith,JaneOtherActivity12/16/13 9:22:15 AM12/16/13 10:06:55 AM0:44:40

Query to break out the total duration time in to the 15 minute interval it fell into

EmployeeActivityIntervalIntervalDuration
Doe,JohnSomeActivity12/16/13 9:00:00 AM0:08:58
Doe,JohnSomeActivity12/16/13 9:15:00 AM0:15:00
Doe,JohnSomeActivity12/16/13 9:30:00 AM0:15:00
Doe,JohnSomeActivity12/16/13 9:45:00 AM0:12:14
Smith,JaneOtherActivity12/16/13 9:15:00 AM0:07:45
Smith,JaneOtherActivity12/16/13 9:30:00 AM0:15:00
Smith,JaneOtherActivity12/16/13 9:45:00 AM0:15:00
Smith,JaneOtherActivity12/16/13 10:00:00 AM0:06:55

View 2 Replies View Related

Tables :: Phone Statistics - Formatting Field To Show Duration Not Time

Apr 13, 2014

I am building a database to enter staff phone statistics. As an example my fields would be - Name, Date, Staffed time, Available time, Aux time and then calculated fields to show the percentage of time i.e %Aux, %Available etc.

My problem is the formatting of the times entered as they are duration not time. Say staffed time is entered as 08:00:00 for 8 hours and Aux time 03:57:21. The only format I can see to suit is date time but then Access takes these entries as 8am and 3:57am is there a way to change this to work as duration hh:mm:ss?

View 6 Replies View Related

Convert Listed Dates To Duration To Calculate Equipment Rental Fees

Apr 7, 2015

I'm new in access I working on DB for my company equipment

I used tables and queries and reached to the following query

Query name (02-By Project)

KSC_NO From_Project To_project Date_of_Trans
K-01-037 52500 12013 02/14/2014
K-01-037 12013 52500 03/31/2015
K-01-037 52500 12013 04/07/2015
K-01-129 52500 12013 03/03/2015

I want to make query to give me the following

KSC_NO IN_Date Out_date Duration (months)
K-01-037 02/14/2014 03/31/2015 13.6667
K-01-037 04/07/2015 Today 0.0333
K-01-037 03/03/2015 Today 1.2

Is that possible?

View 5 Replies View Related

Program Working In Access 2007 Not Working In Access 2010 Due To Missing OCX File

Dec 27, 2014

I have a program that runs under access 2007 that I use at my work. We will soon be updating to MS office 2010 and the program will not work now because a calender file .ocx was removed from access 2010. Is there a way to get the 2007 .ocx file to work in access 2010?The program I am using is a relatively simple stand-alone and unsupported app that we use to request patient arrival and departure from various radiology tests inside a hospital. No reports are made from the app other than the number of patient transports for the day.

The app is placed on a common drive accessed from any pc in the hospital. No special permissions are required. But our app does use the calendar, time and date functions in access 2007. When I tried the app on a pc with access 2010, it basically says it (access) cannot open the app because a .ocx file is not present.Is there a way to make the access 2010 calendar file work in access 2007?

View 1 Replies View Related

Duplicate Record Command Button Not Working For One Form But Is Working For Other Form

Jan 15, 2015

I have an Access 2010 database with two tables and two forms. The tables are Organizations and People. Similarly, the forms are Organizations Entry Form and PeopleEntryForm. The People are linked to the Organizations table. Several people can be linked to the same organization.On my Organizations EntryForm, I created a command button to duplicate a record using the wizard. It works fine.

I did exactly the same thing on the PeopleEntryForm, but instead of copying the record, it creates a new blank record. I don't get any error messages. Is my problem due to the fact that the People table is linked to the Organizations table?

View 13 Replies View Related

Tables :: Get Total Of Clip Size And Also Total Of Clip Duration

Dec 31, 2012

I have a table with 4 field which describe clips.

ClipName (txt)
ClipSize ( Long Integer)
ClipDuration (HH:MM:SS)
Date created (dd/mm/yy).

I would like to get the total of ClipSize and also total of Clip Duration either in table down or in query.

View 13 Replies View Related

Working Out How Old Someone Is

May 10, 2005

Hi all,

I'm making a simple query to tell me how old a list of members are in a database.

I figure as I have their birthday, armed with todays date access should be able to tell me (in years only) how old they are.

Any help is appreciated

View 14 Replies View Related

MDE Not Working?

Jul 19, 2005

Hi,

I have 2 computers (computer 1 with Office XP and computer 2 with Office XP SP1). An MDE file created from an MDB file on computer 1 will not work on computer 2 (run time error), but an MDE file created on computer 2 also does not work on computer 1 (error message was "database corrupted").

Does anyone know why and what I can do to make the MDE work on both machines?

View 6 Replies View Related

MDE Not Working

Jul 21, 2006

Hi. I have got a small database, I have split it into front and back end. I then try to make an MDE out of the front end and after going to tools>db utilities>make MDE file, i got the save as box open up and i pressd save to my desktop,the box disappears and the screen is just blank(like there is no database file has been open,at the bottom of Access it says Make MDE/ADE.
And it just crashes.. it doesnt come up with any errors.
If anyone has any information pleeeeeaaaseee help.......

View 8 Replies View Related

Cbo Not Working Well

Aug 2, 2005

My Combo Box works and displays new entries in text boxes in my form. The selection appears in every record. I would like the selection to be stored only in the displayed record and not for each and every record. Any ideas?

My combo box works on an existing query( Qry1). I would like the data from my query (Qry1) to be displayed in the record (in Form1) which I am viewing. When I go onto a new record (IN Form1) I would like to make a new selection from my query (Qry1) and then store these details in the new record in Form1.

I am a little frustrated - please help.

View 3 Replies View Related

Sum() Not Working

Sep 20, 2006

This seems so simple, but, being a novice, I'm scratching my head.

I'm using an unbound text box in the form footer to sum the value of a column in the form ( =Sum([LineTotal]) ). [LineTotal] evaluates correctly, yet the sum is always #ERROR. Is there something I am missing, or am I correct in thinking that this should work?

Thanks to anyone who can shed some light.

-Don

View 7 Replies View Related

Why Is This Not Working ?

Nov 8, 2004

I open a form that is bound to a linked SQL table. On that form a have a list box that shows a series of records based on a query. The listbox DOES contain the PK field "Record_date" in its query.

I am expecting that if I click on a given record in the list box the form would change to that matching record. What am I doing wrong ?

List box code:

Private Sub List212_AfterUpdate()

' Find the record that matches the control.
Dim rst As Object
Set rst = Me.Recordset.Clone
rst.FindFirst "[Record_date] = #" & Format(Me![List212], "mm/dd/yyyy") & "#"
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

End Sub

If I change the form record and click the list box, its always takes me back to the 1st record - not the matching record selected in the form.

Help ?

View 3 Replies View Related

Why Is This Not Working??

May 8, 2005

Hy,i've got three tables:
products:
product_id,product_name
months:
month_id,name_of_month
calculations:
calculations_id,month_id,amount

Also have this query:

SELECT DISTINCTROW products.product_name, Sum(calculations.amount) AS [Sum Of amount]
FROM products INNER JOIN calculations ON products.product_id=calculations.product_id
WHERE calculations.month_id in (forms!frmMyForm!txtMyTextbox.value)
GROUP BY products.product_name;

My problem lies in this part of query :
WHERE calculations.month_id in (forms!frmMyForm!txtMyTextbox.value).

I've got problem when i want to get values from my textbox on my form.
If i instead (forms!frmMyForm!txtMyTextbox.value) put (1,2) my query works fine,but when i wanna get this same values from textbox on my form it returns me an error.
I have also noticed one more thing:If i put in my textbox just value 1 it works fine,but if i use more values separeted by comma instead of result it returns me an error.
Why is this,and how to avoide this problem?
Thanks!

View 8 Replies View Related

Why Isn't This Working

Jul 29, 2005

Private Sub propertypass_Click()

If [Forms]![UpdatedFullInventory]![PAM_ID] = [Tables]![UpdatedPropPassInventory]![PAM_ID] Then
MsgBox "A property pass for PAM ID " & PAM_ID & " has already been created. Please search the property pass database for more information!", vbOKOnly, "Error!"
End If

My debug message says

Microsoft can not find the field 'l' referred to in your expression.

View 1 Replies View Related

Breakpoints Not Working

May 13, 2005

Hello,

For osme reason this MS Access database I'm working on will not let me debug it. First off, even if I mistype a variable name it is not breaking and giving me an error message. I checked and I do have 'Option Explicit' set as well as "Error Trapping - Break on All Errors" in the Tools - Options - General.

Also, when I set a breakpoint in my code, it doesn't work either.

Any ideas as to what is going on??

Thanks,

Brian

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved