Continuous form "#Error" in new record



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:


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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question