Continuous form "#Error" in new record

[JackieB]

I am working with Access 2003. I am creating time sheets. When calculating
times into hours worked for each day I receive an "#Error" in a new record.

This is my calculation in the page footer Text box name "Total":

=HoursAndMinutes(IIf([OutTime] Is Null,[OutTime2]-[InTime2],IIf([OutTime2]
Is Null,[Outtime]-[InTime],IIf([OutTime2] Is Not
Null,[Outtime]-[InTime]+[OutTime2]-[ InTime2],IIf([InTime] And [OutTime] And
[InTime2] And [OutTime2] Is Null,"00:00")))))

This is my caluculation in Subform Text box name "Text35":
=IIf([total] Is Null,"00:00",[total])

Example of end product= "8:00"

New entry on the above works perfect

This is my Calucalation in Subform to convert to fraction of day:

=FormatNumber([Text35],6)

Example of end product=".333333"

New entry ="#Error"

Calculation in subform to convert to hours:

=Round([Fraction of day]*24,2)

Example of end product= "8.00"

New Entry = "#Error"

I have tried many different things and always have a return of "#Error" in
new entry.

Please help

 

[Ken Snell MVP]

A subform can reference a control on its parent form if you tell the subform
that the control is on the parent form:

=IIf([Parent].[total] Is Null,"00:00",[Parent].[total])


In this case, though, it's possible that you still will not get a value
because the subform may be looking for the data from the parent's form's
textbox BEFORE the parent form renders the value in the Total textbox in the
page footer of the parent form.

You may need to put a hidden textbox (call it txtText) in the parent form's
PageHeader section, let that textbox do the calculation, and then set the
PageFooter's text box's ControlSource to be the hidden control. Then the
subform should reference the hidden textbox and not the Total textbox. It
all depends upon what data the main form is using/calculating, and what data
the subform is using/calculating.