If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Error Unbound text calc time
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] -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Error Unbound text calc time
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. -- Wayne Morgan MS Access MVP "chopper57 via AccessMonster.com" u17099@uwe wrote in message news:5cc254adb8ef4@uwe... 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] -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Error Unbound text calc time
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. Wayne Morgan wrote: 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] -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200603/1 |
#4
|
|||
|
|||
Error Unbound text calc time
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. chopper57 wrote: 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] -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200603/1 |
#5
|
|||
|
|||
Error Unbound text calc time
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] chopper57 wrote: 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] -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200603/1 |
#6
|
|||
|
|||
Error Unbound text calc 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. -- Wayne Morgan MS Access MVP "chopper57 via AccessMonster.com" u17099@uwe wrote in message news:5cc3638650c80@uwe... 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] chopper57 wrote: 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] -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200603/1 |
#7
|
|||
|
|||
Error Unbound text calc time
Thanks Wayne, that worked great. Thanks for your time.
Wayne Morgan wrote: 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] -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
Error Unbound text calc time
Thanks Wayne, that worked great. Thanks for your time.
Wayne Morgan wrote: 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] -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Newbie table Layout (Posted as suggested by Tom Lake for feedback) | Little Penny | Using Forms | 2 | December 25th, 2005 04:44 PM |
Word applies direct format on File open | Uriel | General Discussion | 16 | November 27th, 2005 07:22 PM |
Add New Field to DB | Karen | Database Design | 7 | October 19th, 2005 08:03 PM |
Change font of part of text | John | Powerpoint | 7 | March 15th, 2005 10:10 AM |
Access reports with a horizontal line after each record??? | Bill via AccessMonster.com | Setting Up & Running Reports | 6 | March 9th, 2005 04:51 PM |