Update query doesn't like time calculations

[UnsolvedCoding]

I am still working on a timesheet project and have been having a lot of problems getting an update query to work when calculating times. I can get it to run just fine as a select or append query but when I try to make it a select query it won't run.

This particular issue has to do with calculating the regular work hours during the day, taking the start of the day and the start of lunch to get a time and getting the end of lunch and the end of day to get a time.

I have tried Datediff but when I combine the two time segments to get total hours worked it crashes. I have tried startlunch-startday + endday-endlunch but that crashes also. And I have tried creating a query to calculate the information - which works fine, but when that query is introduced the update query crashes.

Sample of code from query that works AM_hrs: ([Time_Table]![Start_Lunch]-[Time_Table]![Start_Day])*24

Hours: DateDiff("n",[Time_Table]![Start_Day],[Time_Table]![Start_Lunch])/60

Both fail.

Anyone know a good way to calculate the times?

 

[dhookom]

Do you have any fields that are null?
Why/what are you updating? Are you attempting to store a value that can be calculated when needed.

Duane
Hook'D on Access
MS Access MVP

 

[UnsolvedCoding]

No fields are null.

This is part of a project to totally overhaul our current timesheet system. In other words build a new timesheet that will hold reports for vacation, time off like medical leave, adding new employee information etc.

When the form has the start of day button clicked the table holding the information gets their name, ID and the time they clicked the button entered. When they go to lunch the table holding the information is updated with a time stamp for when they clicked they are starting lunch. So far so good.

The summary table now gets update and because its the first entry they can do for the day an append query (insert statment) is used and everything is fine.

The user would then click a button to indicate they are back from lunch and another to indicate when their day is over. The summary table would be updated to show the total hours worked at this point but the update fails. Access keeps protesting about updating hours worked even though the information is stored in a different table.

 

[dhookom]

Can you provide your table structures and form recordsources?

Duane
Hook'D on Access
MS Access MVP

 

[UnsolvedCoding]

The only information that is used for a form is the one that shows the employee's name, id and which group they are in. Names are text, IDs are numbers and the group is a yes/no. Each field has been carefully reviewed several times in all tables because that is one of the first things I thought of when it came to debugging.

Even with a query if I try to go to datasheet view and simply view results, if its an update query I can't get even that far using either of the above formulas.