Get rid of or change #Error message in text box

[davea]

Hello

I have a form that calculates totals based on contents of fields from a
subform. It works fine but if the subform has no data to add up the
text box(s) on my main form display "#Error". I know why it does this
but need to change "#Error" to "0".

Here is my calculated field code on the main form:

=(EmployeeDetailssubformi.Form!CostRate)*[HoursEmp1]

If there is nothing to calculate this field displays "#Error". I need
it to display "0".

Anyone got any suggestions?

 

[Allen Browne]

If a form has no records to display, and no new records can be added, the
Detail section goes completely blank. Then trying to refer to the
non-existent text boxes gives the error.

You can solve the problem if you allow new records (e.g. set the form's
AllowAdditions property to Yes.) You can still prevent new records by
cancelling Form_BeforeInsert.

If the form is based on a read-only query, that won't work. You will need to
test if the form has records by using an expression such as this:
=IIf([EmployeeDetailssubformi].[Form].[RecordsetClone].[RecordCount] >
0,
[EmployeeDetailssubformi].[Form]![CostRate])*[HoursEmp1], 0)

 

[davea]

Thanks Allen

Works perfectly now, used the RecordSet > 0 in IIF statement

Thanks again for quick response

David

 

[davea]

Can I apply the same method if I want this form duplicated in a report?

 

[Allen Browne]

In a report, you test the HasData property.

Details in:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

 

[davea]

Thanks...again!

Got that working now.

I read on your site the section regarding "Total does not work in the
subreport.....If the basic =Sum([Amount]) does not work in the
subreport:"

I have fields which total all my subform calculation fields in the
detail section and have named them SubTotal1 to 5. I'm trying to show
an overall total for all the records in my report footer and tried:

=Sum([SubTotal1]+[SubTotal2]......[SubTotal5]) but when I run the
report it prompts me to enter SubTotal1 to 5. I f I click past them the
total just comes out as 0. I have my overall total field, called
[GrandTotal], in the report footer section like you said but it still
doesn't work. Any advice (again!)?

 

[Allen Browne]

I take it that:
a) Your subreport has a bound field named (say) Amount.

b) The Report Footer section of your subreport has a text box with these
properties:
Control Source =Sum([Amount])
Name SubTotal1

c) There are 4 more similar fields and text boxes.

d) There is a 5th text box in the subreport's Report Footer section, with
Control Source of:
=Sum([SubTotal1]+[SubTotal2]......[SubTotal5])

Change the Control Source of the 5th text box to:
=Nz([SubTotal1],0) + Nz([SubTotal2],0) + ... + Nz([SubTotal5],0)

Using Sum() is not correct in this context.
The Nz() is necessary so you get a result, even if one of the totals is
blank.
You also need to set the Format property of this text box (and the previous
5) to Currency or General Number, or something that tells Access it is a
numeric value.

Once you have that working in the subreport, you will be able to bring the
total back onto the main report if you want to do that.

 

[adorico]

how to remove error in my text box