Error Unbound text calc time

[chopper57 via AccessMonster.com]

I have the following problem with a database that I am working on, the
database is for daily reports and it tracks employee hours on the job site.
I have a subform for entering: Employee Name, Start Time, Finish Time, Breaks,
and an Unbound text to calc the time. I have the start, finish and break set
with default values so the calc time shows 10 Hrs. The calc works, the
problem is when you fisrt choose a name from a dropdown list the next empty
record in the calc field shows #Error. I can continue to add names and the
calc works in each record but the last empty record is always #Error. Now if
I forward the form to a new or previous record and return to the present
record the #Error is gone and shows 10. I think this problem is also showing
the #Error in my report when there's no work for a day with no names selected.


This is the formula in the unbound text in the Control Source on the subform .

=IIf([Finish]>[Start],([Finish]-[Start])*24,24-([Start]-[Finish])*24)-[Breaks]

 

[Wayne Morgan]

What is in Start, Finish, and Breaks if there is no record, are they Null?
If so, Null should be the result, not Error. You're not dividing, so the
error shouldn't be a divide by zero error. Are the field names and control
names the same (i.e. is the control called Finish bound to a field called
Finish)? However, this will usually give a Name error.

Also, I see you're adjusting for going past midnight by subtracting 24. I
would recommend that your times include the date and time. You could then
just use the DateDiff() function and Access will give you the correct
result, automatically compensating for going past midnight.

 

[chopper57 via AccessMonster.com]

The Start, Finish, and Breaks all have defaults, Start is 07:30PM, Finish is
06:00AM and Breaks is 0.50 for half hour. and the calc in the text box is 10,
which is correct. The Field names come directly from the table and they are
Start, Finish, Breaks. I need the unbound box on the form so I can see the
total hours. All my work is during the night from 7:30PM to 6:00 AM, but we
also have a day shift.

I was wondering if it's a requery problem, because when I go forward of
backward to a new record on the main menu the error goes away.

What is in Start, Finish, and Breaks if there is no record, are they Null?
If so, Null should be the result, not Error. You're not dividing, so the
error shouldn't be a divide by zero error. Are the field names and control
names the same (i.e. is the control called Finish bound to a field called
Finish)? However, this will usually give a Name error.

Also, I see you're adjusting for going past midnight by subtracting 24. I
would recommend that your times include the date and time. You could then
just use the DateDiff() function and Access will give you the correct
result, automatically compensating for going past midnight.

I have the following problem with a database that I am working on, the
database is for daily reports and it tracks employee hours on the job

[quoted text clipped - 20 lines]

=IIf([Finish]>[Start],([Finish]-[Start])*24,24-([Start]-[Finish])*24)-[Breaks]

 

[chopper57 via AccessMonster.com]

I added a requery in the Name Combo box and that took care of the #Error.
But now I still trying to figure out why the form shows error when I have no
names for a day when no-one works.

The Start, Finish, and Breaks all have defaults, Start is 07:30PM, Finish is
06:00AM and Breaks is 0.50 for half hour. and the calc in the text box is 10,
which is correct. The Field names come directly from the table and they are
Start, Finish, Breaks. I need the unbound box on the form so I can see the
total hours. All my work is during the night from 7:30PM to 6:00 AM, but we
also have a day shift.

I was wondering if it's a requery problem, because when I go forward of
backward to a new record on the main menu the error goes away.

What is in Start, Finish, and Breaks if there is no record, are they Null?
If so, Null should be the result, not Error. You're not dividing, so the

[quoted text clipped - 12 lines]

=IIf([Finish]>[Start],([Finish]-[Start])*24,24-([Start]-[Finish])*24)-[Breaks]

 

[chopper57 via AccessMonster.com]

The problem on my report is to show Previous Hours when no Hours are entered.
Previous Hours works fine as long as there is one entry or more. Need it to
work with no entries.

Previous Hours on Main Report
=[subrpt_AccumutiveHours].Report.AccumutiveTotal-[subrpt_CalcHours].Report.
[Hours Grand Total Sum]

subrpt_AccumutiveHours – AccumutiveTotal Control Source is:
Accumative Total is running total of hours from all records
=Sum([Hours])

subrpt CalcHours Hours – HoursTotalSum Control Source is:
HoursTotalSum is total hours from last record entered (could be no hours when
no work performed)
=Sum([Hours])

“Hours” in the subrpt’s comes from a query with the following formula:
Hours: IIf([Finish]>[Start],([Finish]-[Start])*24,24-([Start]-[Finish])*24)-
[Breaks]

I added a requery in the Name Combo box and that took care of the #Error.
But now I still trying to figure out why the form shows error when I have no
names for a day when no-one works.

The Start, Finish, and Breaks all have defaults, Start is 07:30PM, Finish is
06:00AM and Breaks is 0.50 for half hour. and the calc in the text box is 10,

[quoted text clipped - 11 lines]

=IIf([Finish]>[Start],([Finish]-[Start])*24,24-([Start]-[Finish])*24)-[Breaks]

 

[Wayne Morgan]

The question is what value is in the textbox when there are no records. If
the value is Null, you may be able to wrap the reference to the
field/control with the Nz() function to change it to zero.

Example:
=Nz([subrpt_AccumutiveHours].Report.AccumutiveTotal, 0)-
Nz([subrpt_CalcHours].Report.[Hours Grand Total Sum], 0)

Another possibility is the IsError() function.

Example:
=IIf(IsError([ControlName]), "Error", "Not Error")

Replace the text values above with what you would like to see if there is or
is not an error.

--
Wayne Morgan
MS Access MVP

The problem on my report is to show Previous Hours when no Hours are
entered.
Previous Hours works fine as long as there is one entry or more. Need it
to
work with no entries.

Previous Hours on Main Report
=[subrpt_AccumutiveHours].Report.AccumutiveTotal-[subrpt_CalcHours].Report.
[Hours Grand Total Sum]

subrpt_AccumutiveHours - AccumutiveTotal Control Source is:
Accumative Total is running total of hours from all records
=Sum([Hours])

subrpt CalcHours Hours - HoursTotalSum Control Source is:
HoursTotalSum is total hours from last record entered (could be no hours
when
no work performed)
=Sum([Hours])

"Hours" in the subrpt's comes from a query with the following formula:
Hours:
IIf([Finish]>[Start],([Finish]-[Start])*24,24-([Start]-[Finish])*24)-
[Breaks]

I added a requery in the Name Combo box and that took care of the #Error.
But now I still trying to figure out why the form shows error when I have
no
names for a day when no-one works.

The Start, Finish, and Breaks all have defaults, Start is 07:30PM, Finish
is
06:00AM and Breaks is 0.50 for half hour. and the calc in the text box is
10,

[quoted text clipped - 11 lines]

=IIf([Finish]>[Start],([Finish]-[Start])*24,24-([Start]-[Finish])*24)-[Breaks]

 

[chopper57 via AccessMonster.com]

Thanks Wayne, that worked great. Thanks for your time.

The question is what value is in the textbox when there are no records. If
the value is Null, you may be able to wrap the reference to the
field/control with the Nz() function to change it to zero.

Example:
=Nz([subrpt_AccumutiveHours].Report.AccumutiveTotal, 0)-
Nz([subrpt_CalcHours].Report.[Hours Grand Total Sum], 0)

Another possibility is the IsError() function.

Example:
=IIf(IsError([ControlName]), "Error", "Not Error")

Replace the text values above with what you would like to see if there is or
is not an error.

The problem on my report is to show Previous Hours when no Hours are
entered.

[quoted text clipped - 33 lines]

=IIf([Finish]>[Start],([Finish]-[Start])*24,24-([Start]-[Finish])*24)-[Breaks]