Hi

I have a problem with the mtd and ytd formula in a select query. Basically I use the following forumula to calculate these the month and ytd totals. However the formula turns the negative numbers to positive which gives eronious results. I tried to take the Absolute reference out but this only serves to make all the values negative. Is there some way to tweek this formula to produce valid month and ytd calculations?

YTDActual: Abs(Sum((Year([Period])=2010 And Month([Period])>=7)*[Net Value]))

MTDActual: Abs(Sum((Year([Period])=2010 And Month([Period])=Month(forms!switch!cboMth))*[Net Value]))

This is what the SQL looks like if this helps further.



Would appreciate any push in the right direction on this problem.

Take care

Chad
Õ¿Õ

Might be easier to explicitly give the field when the condition is true via an IIf statement:

YTDActual: Sum(IIf(Year([Period])=2010 And Month([Period])>=7),[YourFieldName]*[Net Value],0)

MTDActual: Sum(IIf(Year([Period])=2010 And Month([Period])=Month(forms!switch!cboMth)),[YourFieldName]*[Net Value],0))

So what the above "should" do is when the Period field matches your criteria, then the field that contains the value is multiplied by the Net Value field. If it does not meet that criteria then it returns a zero to add in the sum (in effect negating it from the summing). Same for the second one.

Chad: Since the first part of your formula (Year([Period])=2010 And Month([Period])>=7) turns out -1 for true and 0 for false, and it seems you want to transform that to 1 or 0, try putting the Abs around just that part of the formula...instead of around the whole thing.


YTDActual: (Sum(Abs(Year([Period])=2010 And Month([Period])>=7)*[Net Value]))

This should total all the results of 1 or 0 times [Net value] instead of making all the results into absoulte values.

HTH

Joe

Thanks for the assistance guys. Great suggestions and it really helped. Perfect actually. Much appreciated.

Take care

Chad