SubForm Sum Calculation

[dazstarr]

Hi

I have a form with a sub-form embedded within it.

On the main form, I have 2 date fields, when a user enters the dates - the subform updates and shows records applicable within those dates.

One of the fields in the subform is a Currency field.

What I would like to do is to show the sum value for all values in this currency field.

If I create a text box on the main form and reference it as:

=Forms!frmInvoices!subPDSCalc!TotalVariation

it shows the first value from the first record.

If I try and put a sum in the above statement - i just get #Error.

Please can someone show me the correct syntax to use?

Many thanks in advance.
Daz

 

[Mike375]

Not sure if this is what you are after but.....

On the sub form do =Sum([FieldName]).

Then on your main form do in an unbound text box

=Forms!MasterForm!SPP!Text66

I jusrt copied that from one of mine and with SPP being the subform and Text66 being the =Sum on the sub form.

 

[dazstarr]

Hi Mike

That worked perfectly! Many thanks.

I have one more question (not sure if this should go into a new post).

If I have a negative number in the currency field, I would like the SUM function to ignore it - better still if there is a negative number - is there anyway it can show Zero?

Many thanks for your help.
Daz

 

[DCrake]

In the control source enter

=iif(Forms!MasterForm!SPP!Text66<0,0,Forms!MasterForm!SPP!Text66)

using the code supplied by Mike

David

 

[dazstarr]

Thanks David/Mike

I have just got back from lunch - so will give it bash.

I will post back asap.

Thanks
Daz

 

[dazstarr]

I have found the exact field where I need to put this IIF statement in, unfortuntely the field in the query calls upon a function in order to calculate.

Here is the field in the query..

DayCalc: HowManyWD([LowerDate],[UpperDate],tblPersonalDailySchedule!Day)

What would be the syntax to implement the IIF in the above? David helped me with the above yesterday.

Many Thanks
Daz

 

[dazstarr]

I have sorted the above problem by creating another calculated field in the query.

I created the following:

TestField: IIf([DayCalc]<0,0,[DayCalc])

The above will display a '0' in the results if the value of 'DayCalc' is a negative number.

Thanks for pointing me in the right direction guys.

I really appreciate your help and expertise.

Thanks again
Daz

 

[Mike375]

Daz,

The IIF() can be like a life boat You can nest them or you can make another field and that can include a field that is an IIF

Another way to play about with only selected records in a column is DSUM. There have been a few posts in the past on negatives of DSum and David could elaborate on that.

Here are a couple

=DSum("[Calories]","Query1","[Food] = 'Apple'")

=DSum("[Calories]","Query1","[Food] = '1 tea/milk/sugar'")

The above examples go into an umbound text box but the underlying query is reference, not the form. In the above the field Calories is being added for the records where the field [Food] has the above entries.

You can play with this stuff for hours and finish witha form that looks like the dashboard from the Space Shuttle

 

[dazstarr]

Thanks Mike

I will have play around with DSUM's. Sounds like they could be quite useful.

Thanks again for your help. I would be stuck (and probably unemployed!) without guys like you.

Daz

 

[Mike375]

You can also do DCount Daz.

Same as above, just replace DSum with DCount.

 

[dazstarr]

So many options I didnt even know exist!

Hehe!

I will have a play around and see what best fits my database.

Cheers Mike!