sum on form with conditions

Hi - I have a continuous form that displays many records. One of the fields is an amount field. In the footer, I want to sum the amount field. That seemed easy enough using an =sum([field_name]). But then I wanted only sum some of the fields where another field (a Y/N field) was set to Y.

I'm sure it's easy, but I can't seem to figure it out. I've tried =sum([field_name]) where ynfield = "Y", but that doesn't work.

Thanks in advance for your help.

Bill Berg

Originally Posted by bkberg05

Hi - I have a continuous form that displays many records. One of the fields is an amount field. In the footer, I want to sum the amount field. That seemed easy enough using an =sum([field_name]). But then I wanted only sum some of the fields where another field (a Y/N field) was set to Y.

I'm sure it's easy, but I can't seem to figure it out. I've tried =sum([field_name]) where ynfield = "Y", but that doesn't work.

Thanks in advance for your help.

Bill Berg

re: sum on form with conditions

Hi

Ther are a couple of ways of doing this.

1) If the record source is based on a stored query, then you could use the DSum() domain function something lke assuming ynField is a yes/no datatype (ie. boolean - in which case you only need ??

2) In the form Record Source query, in query designer, add a field
again assuming ynFiled is Yes/No
then in the footer texbox enter
There is another way (which I've just used to calculate 39 sub total because 39 DSum()s are too slow) but that involves cycling through the records with VBA, which has virtually no time penalty.

Method 2 would be my prefered option.

HTH


MTB

re: sum on form with conditions

Hey - I used option 2 and it was golden!!! Thanks very much.

re: sum on form with conditions

Option 2 is certainly the preferred choice Bill.

I wonder why MTB was using VBA instead :S Any light to throw Mike?

re: sum on form with conditions

Hi Neo

I used code because adding 39 fields to the query seem a bit tedious, and writing the code to do it seemed much more fun (I’m just a code junkie!). Also had to run it in the Form_Current event to update after filtering etc. which of course would be automic using the fields option.

Just thought it fight push the envelope a little, and I might learn something (well maybe)!?

No other reason that that really.

MTB

re: sum on form with conditions

Fair enough Mike. It's always fun to get to play with the code a bit. I'm lucky enough at the moment, to be required to do that for my work. It's a hard life eh?