help with a calculation from sub form to main form (1 Viewer)

[munkeyroot]

Hi Everyone

I hope anyone could shed some light on this.

I have multiple continues sheet subforms with fields including a subtotal, at the bottom in the footer i have placed a text box with =Sum([Labour rate]*[labour hrs]) which as soon as i enter the details in hrs (qty) Labour rate (£0.00) it will give me a sub total on the over all.

Ok so, once this is updated on the subform, the main form takes the sub total from the text box =[frmSubRoleSelect].[Form]![TotalPrice] so i can show this in a summary field.

I also have a few other forms that do this, and the total is displayed on the summary on the main form.

The problem i am having is that not all the forms that are calculated may be filled in, so it will not show a total amount in the subform text box(s) total untill at lease an amount is entered including a zero amount, so consequently my main form summary will not update.

i can see the issue, but not sure how to fix, its like the subtotal text box needs to always show a £0.00 amount even if the rows on the form are not entered.

i hope this makes sence, i may be going about the the wrong way

cheers Munk

 

[CJ_London]

have you tried using the nz function?
=nz(totalprice)+nz(totalpart)....

 

[munkeyroot]

Hi C_J
thank you for your post, ah no i had not mate.

Just had a play about with it, not sure if i'm using it right. :S

if i use the =nz which works for the null to 0, to show the £0.00 on the summary, however it does not give me the total sum of the rows and the hrs entered in the subform even thought he sum is entered.

i've put some screen shots on to show you fella

cheers munk

 

[CJ_London]

looks to me like the problem is on your subform. I can't see the formula for that. Presumably you are expecting a value of 95.50?

 

[munkeyroot]

Hi CJ

Thank you for your comment, i have had another go again. i have figured it out.

so ok before, i was trying to set the =nz on the subforms footer text box, this was causing issues with the calculations of the rows.

so instead i set the =nz on the summary text boxes on the main form so they look like this =Nz(([frmSubRoleSelect].[Form]![TotalPrice]),0)

so this is basically saying if the text box "TotalPrice" on "frmsubRoleSelect" is Null then display "0" . as this the texted boxes are formatted to currency they display a £0.00.

thanks for your guidance CJ

All the best Munk