Calculated field not working

[3Mark3]

Hello everyone,
I'm hoping someone can help me correct my code (which I know isn't done right). Here's what I'm trying to do.

What I'm tring to do is sum up the field "Clundquist". However, I only want it to sum that field if the current month (formatted date part in date_today) is equal to the crosstabs audit date (also datepart). I know I'm not doing this right, can anyone help me fix this code?




=IF DatePart("m",[date_today]=DatePart("m",[audit date]), Sum([Clundquist])))

 

[PHV]

Something like this ?
=IIf(Month([date_today])=Month([audit date]), Sum([Clundquist]),0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[3Mark3]

Thanks for your help. Actually I added a month (datePart) control so that works..probablem it's counting everything.

=IIf([month_day_today]=[month_day],Sum([Clundquist]),67)

What I'm actually trying to accomplish is this:

I want to count all day's records where month_day_today is equal to Month_day.

Right now it just counts everything.

 

[3Mark3]

I guess In the end I'm trying to code the following, although I know I can't use a where in this statement?

=Sum([Clundquist]) where ([month_day_today])=([month_day]))

 

[PHV]

=Sum(IIf([month_day_today]=[month_day], [Clundquist], 0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886