I have a query where I enter the "Job Number", "Normal Time", "Over 

Time", "Total Time"

The three Time columns are formatted to Short Time (ex:  07:21)

I want to add up the hours in the "Total Time" column and group the 

results by "Job Number".

The final time for each job number could be anything from less than an 

hour up to several hundred hours.



With the Hour function I can total the Hours only.

With the Minute function I can total the Minutes only.



I have tried to work out many different options over the last couple of 

days and am still totally lost. I presume that I should create a custom 

function of some sort!!!!!



Any advise will be appreciated.



Regards

Don

If you can wrangle your data such that you get a start time and a stop time

on a single record (w/maybe a cross tab query?), you should be able to pass

those to the DateDiff() function.  This assumes that start time and stop

time always occur on the same day--if not, you've got a much tougher

problem.  See the help file for DateDiff() for details.



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

(xxx) xxx-xxxx



-----Original Message-----

From: DON LOWE [mailto:donlowe@s...]

Sent: Friday, September 28, 2001 8:10 AM

To: Access

Subject: [access] Calculating time in Access







I have a query where I enter the "Job Number", "Normal Time", "Over 

Time", "Total Time"

The three Time columns are formatted to Short Time (ex:  07:21)

I want to add up the hours in the "Total Time" column and group the 

results by "Job Number".

The final time for each job number could be anything from less than an 

hour up to several hundred hours.



With the Hour function I can total the Hours only.

With the Minute function I can total the Minutes only.



I have tried to work out many different options over the last couple of 

days and am still totally lost. I presume that I should create a custom 

function of some sort!!!!!



Any advise will be appreciated.



Regards

Don

Microsoft's knowledgebase has an excellent example how to perform the

function you want. Goto

http://search.support.microsoft.com/kb/c.asp?fr=0

<http://search.support.microsoft.com/kb/c.asp?fr=0&SD=GN&LN=EN-US>

&SD=GN&LN=EN-US and enter Q88657



The article name is "Functions for Calculating and Displaying Date/Time

Values" and it will provide you with the code you need.



John Ruff - The Eternal Optimist :-) 





-----Original Message-----

From: DON LOWE [mailto:donlowe@s...]

Sent: Friday, September 28, 2001 8:10 AM

To: Access

Subject: [access] Calculating time in Access







I have a query where I enter the "Job Number", "Normal Time", "Over 

Time", "Total Time" The three Time columns are formatted to Short Time

(ex:  07:21) I want to add up the hours in the "Total Time" column and

group the = results by "Job Number". The final time for each job number

could be anything from less than an = hour up to several hundred hours.



With the Hour function I can total the Hours only.

With the Minute function I can total the Minutes only.



I have tried to work out many different options over the last couple of

= days and am still totally lost. I presume that I should create a

custom = function of some sort!!!!!



Any advise will be appreciated.



Regards

Don

Thank you for the reply.



I have tried the Sample function on Q88657 Functions for Calculating and 

Displaying Date/Time Values.



I have come up with the following situation.

When declaring db As Database, the Database is not in the 

"properties/methods drop down list.

When I test the function in the Immediate window, an error message with 

"User-defined type not defined".



Please advise what I should do to correct this situation.

Regards

Don

Don,



Delete the two following references in your code:



Dim db as database

Set db = dbengine.workspaces(0).databases(0)



Change

Set rs = db.OpenRecordset("timecard")



To

Set rs = CurrendDB.OpenRecordset("timecard"







John Ruff - The Eternal Optimist :-) 







-----Original Message-----

From: DON LOWE [mailto:donlowe@s...]

Sent: Friday, September 28, 2001 10:46 PM

To: Access

Subject: [access] RE: Calculating time in Access





Thank you for the reply.



I have tried the Sample function on Q88657 Functions for Calculating and

Displaying Date/Time Values.



I have come up with the following situation.

When declaring db As Database, the Database is not in the

"properties/methods drop down list. When I test the function in the

Immediate window, an error message with "User-defined type not defined".



Please advise what I should do to correct this situation. Regards Don

John I am now getting a Run time error 13.



My understanding is that I must do something with the Data format of the 

string ("timecard") to make it compatible with the declared variables.



Do you have some more ideas.

Regards

Don