I have a field in my form that gets the value of a field in a subform, but if there is no data in the subform (it depends on the selection of a listbox in my form) the field gets #error instead of "0". If the subform has data it will show sum([Price]) -> the price total. How can I make this field in my form show "0" instead of #Error in case the subform is empty?

Hope I made myself clear.

Try using the Nz formula when you reference it.

Something like sum(Nz(Price]))

This function changes Null values to 0's

It should work - if not we can try something else.

Mike

Mike
EchoVue.com

=( it didnt work....

Hi,

I use this code and it works fine.

=IIf(IsNull(([subfrmToolShippingTickets].[Form]![Text56])),0,DSum("[LineTotalTools]","tblInvoicesOrdersTool","[InvoiceID]=" & [InvoiceID]))

D. Bartelt

ok... but where should I use it?

I tried the code, but it did not work =( I dont know why, maybe it is because since there is no data in the subform and the field is on its base, the field will not exist until some data is shown....

Lets take a step back... Would you mind giving a brief explanation of the form and subform and how they are related? Maybe there is a different solution that will be easier to see if we have that.

Thanks

Mike

Mike
EchoVue.com

Use this code as the source of the text box:
=IIf(DCount("[Price]", "subformSource") <> 0, DSum("[Price]", "subformSource"), 0)

Change subformSource to be whatever the subform is pulling its records from, a query works best.

Do you have the sum([Price]) field in the subforms footer? If so what is the text box name? Not the control source. Also what table is the subform data stored?

D. Bartelt

Use the formula

=IIf(IsNull(([subfrmYourSubFormName].[Form]![YourTextBoxName])),0,DSum("[Price]","tblYourTableName","[YourID]=" & [YourID]))

as the control source for the text box on your main form. That way if the subform doesn't have any data, "0" will be displayed on the main form.

D. Bartelt


D. Bartelt