Calculating over time across midnight issues

[ewilson378]

Hi all,
I'm a new group member, self taught and Access casual user and in attempting to help a co-worker manage an Excel spread sheet that it's out of control because it's used by multiple users and every now and then someone screws it up, I committed to creating a database to better manager her data. I created a very basic and simple database (attached here) according to info provided but ran into trouble of calculating hours across midnight to which I found a solution from an old post here. However, the Total OT calculation which was working before, now it's given me this "#Type!" error.
I've checked data types, formatting, etc. What am I missing? Any help is appreciated.

[June7]

I opened the report and not seeing the error. What should I do to reproduce it?

[Mick99]

Hi mate, I have had a quick play please see attached. Have a look and see if that helps, i bit of confusion around using the word "ROUTINE" as a field in a table and also as a calculated field in the query. I have renamed the query and used the query instead of the table on the subform. Your report works fine because it's pulling straight from the query. Hope this helps!! Maybe I missed the point.


Staff OT_PHarding - MICK.zip

Cheers Mick

[ewilson378]

Hi folks. Thanks for the quick response and I apologize for my lack of clarity.
I have been working using the WorkHours subform embedded in the Employees form, not the query.
Mick - thanks for help but if you notice in the subform, the dates that cross midnight, 9/20/15, 10/12/15 and 10/12/15, the calculations are working correctly in ROUTINE (I know the label is confusing, but I didn't name this), but when you go to TOTALOT, it's supposed to add ROUTINE (over time hours worked) and NFR (again, I didn't name this, not sure what this means), but notice that these sums are incorrect, 15:00 (should be 9:00), 14:00 (should be 10), 8:30 (should be 15:30)respectively for dates above.
Again, I created the query and report, but NOT working with them for the purposes of my request for assistance, but thankful for the suggestions.
Once everything works, then I'll create query from the subform and report from that query.
Ultimately I want to be able to calculate and show in the report at bottom of each worker, sum of ROUTINE (ot hours worked), NFR, and TOTALOT, AND totals of these 3 for each Department.
I hope this helps you help me. Thanks again

[June7]

Calculations with date/time values that cross midnight must include the date parts as well, not just time parts. You are saving only time parts in the IN/OUT fields. Access uses a default date of 12/30/1899 if no date is provided.

Options:

1. save the full date/time value in each field

2. an IIf expression to add 1 day if the OUT time is before the IN time


Date is a reserved word - reserved words as names can cause issues - should avoid.

[ewilson378]

Hi again,
June7 thanks but the current format and expressions are working fine, I'm trying to make this database for the end user as simple as possible that would reduce any possible errors.
This database is exclusively to report over time (OT), not beginning and end of work shifts.
Please ignore the query and the report, look at subreport.
The current issue is TOTAL OT is not calculating right and I can't see the problem because all fields have same format.
Help anyone??

[June7]

There is no subreport - did you mean subform? That is where the error shows.

Okay, I have seen this issue before when Format() function is used. Format returns a text string. So your expression calculating with the formatted value summed with a Date/Time value fails.

Try: =CDate(Format([IN]-1-[OUT],"Short Time"))

[ewilson378]

Wohoo!! YES! Now it works. Thanks June7!! Much appreciated.