Showing a zero (2000)

Topic

New Reply

I want a form field to show zero even when it should show a #Div/0! error message. I have the field doing a calculation to show the wage per hour in two places, one based on the employer’s information from the pay stub, and the other based on the employee’s own records of how many hours they worked in a pay period. However, we have not received this information from all the employees yet. Until we do receive the info and input it, this field needs to just show zero. I tried this code in the properties of the event procedure:

Private Sub EE_Wage_per_hr_AfterUpdate()
If EE_Wage_per_hr.Value <= 0 Then
EE_Wage_per_hr.Value = 0
End If
End Sub

But it doesn't return anything other than the same error message. Obviously, I am new to this, so any help would be appreciated.

Thanks.

Reply | Quote

Replies

If the form has a query as the recordsource then you could put this calculated field in the query and just reference it on the form.
In the query put a field:
EE_Wage_per_hr: Iif(HoursWorked=0,0,Wage/HoursWorked)

Substitute your field names for HoursWorked and Wage in your query.

Pat

Reply | Quote

I assume that EE_Wage_per_hr is a calculated control, i.e. the control source is a formula like =[AmountPaid]/[HoursWorked]

If that is correct, the AfterUpdate event of this control will never fire – AfterUpdate only occurs when the user changes the contents of the control directly (not as a result of changing another control), and that is not possible for a calculated control.

Instead, change the control source. Using the field names from the example above, it would become
=IIf([HoursWorked]=0,0,[AmountPaid]/[HoursWorked])
Obviously, you should substitute the appropriate field names.

If my assumption is wrong, post back and tell us a bit more about the way your calculation of wage per hour is set up.

Reply | Quote

Yes, it is a calculated control. The control is: =(([Gross_Pay]+[Bonus_Amt])-([Deduction1_Amount]+[Deduction2_Amount]))/[EE_Record_Hours]

So where do I show the “if” statement that you gave me? In a query, as suggested above? Or in the form field – before or after the control? I assume this is much easier than I am making it. Thanks for any insight.

Reply | Quote

OOPS, got it. Thanks. My form now shows zero if the result is less than zero. I changed the control source, as you suggested, to read: =IIf([EE_Record_Hours]=0,0,(([Gross_Pay]+[Bonus_Amt])-([Deduction1_Amount]+[Deduction2_Amount]))/[EE_Record_Hours])

Thank you, thank you!

Reply | Quote