Trouble with Date Fields (sum)

[txboy]

Hello all,

I have a table where employees enter their begin and end work times.
Those two fields are calculated (in a seperate query) to give the total number of hours worked for each employee that day called "TotalHours".

In the table, the Begin Time and End Time are both date/times fields.
the TotalHours field in my query (listed below) calculates the total hours worked. This all works fine until I try and build another query to sum all the lines in my table.

TotalHours: Format([BeginOtHrs]-1-[EndOtHrs],"h\.nn")

My question is: I'm trying to make another query which will sum the total hours for all employees for a given date range but I keep getting an error message about the "totalHours" field being an invalid data type.

Can someone help me figure this out?

Thanks,

 

[Pat Hartman]

You have formatted TotalHours so that it is a string. You can't sum a string value. Keep the individual differences in minutes and then sum the minutes. You can convert back to hours:minutes for display in the reporting step.

 

[txboy]

Thanks Pat! I will try that and post back.

 

[txboy]

Pat, I'm having trouble with the work around you suggested. Can you detail what it is I need to do to get my totals???

"Keep the individual differences in minutes and then sum the minutes. You can convert back to hours:minutes for display in the reporting step."


Thanks,

 

[sbenj69]

TotalHours: Format([BeginOtHrs]-1-[EndOtHrs],"h\.nn")

My question is: I'm trying to make another query which will sum the total hours for all employees for a given date range but I keep getting an error message about the "totalHours" field being an invalid data type.

Can someone help me figure this out?

Thanks,

Just convert the hours to decimal representation:

In the first query you would have your starttime and endtime, then an expression such as:

LineTotalHrs: Format(DateDiff("n",[Starttime],[Endtime])/60, "Standard")

Note: Generally I would round instead of format, but let's continue.....

in your second query where you are doing totals:

TotalHrs: Sum([LineTotalHrs])


Edit: To do rounding instead of formatting, the expression, LineTotalHrs would be this:
LineTotalHrs: round(DateDiff("n",[Starttime],[Endtime])/60, 2)

 

[txboy]

I tried that but I get negative numbers when my time span crosses over midnight. Is there a simple solution for this issue?

Thanks for your help!

 

[khawar]

http://www.access-programmers.co.uk/forums/showthread.php?t=153471
In the above thread I have uploaded a sample for same problem download the attachments and have a look at them

 

[sbenj69]

I tried that but I get negative numbers when my time span crosses over midnight. Is there a simple solution for this issue?

Thanks for your help!

well, if you're spanning across midnight, then you should be including the date with the time. Either in separate fields or by making it general date type format of your date/time field. However, you will need to parse out the time and the date separately for general date type fields, if you don't have them entered correctly (eg 1/1/2007 5:00:00 am)..... For something like timecards, I always use separate dates and times for ease of use.

Here is a sample

 

[txboy]

Thank you guys for your solutions, however I am using an older version of Access (97) and cannot open the zipped files you have sent. Not sure where to go now.

 

[sbenj69]

see if this works for you:

 

[txboy]

Thank you for converting that database for me. I was able to open the sample you sent, however when I try and run the query i get the following error.

Undefined function 'round' in expression.

any suggestions?

 

[sbenj69]

well, maybe this will work....

 

[txboy]

Thanks, That works great. The only other questions is.... I have the date and times in seperate fields. Is there an easy way of combining those fields without screwing everything up?

Should I try to combine the date and times in the master table or will a query do the trick?

Sorry to be a pain

 

[namliam]

[datefield]+[timefield]

Tho a date/time field is just that for a reason... to not split it! A query that just adds the fields like above will do the trick.