-
1. Re: sum(iif([date]==today(),[arbitrary variable 2],0))
James Baker
Apr 18, 2012 4:03 PM
TODAY() is defined as NOW() rounded to the start of today, so that's the first issue since [date] is a datetime and not a date.
Beyond that, it's possible that basic datetime equality comparison might run into floating-point comparison issues under the hood. You could experiment with using the DATEPART functions to compare the year, month, and day separately, and see if that fixes anything. Let me know.
-
2. Re: sum(iif([date]==today(),[arbitrary variable 2],0))
Just to expand James's comment, you probably need:
sum(iif(DATETRUNC('day',[date])==today(),[arbitrary variable 2],0))
-
3. Re: sum(iif([date]==today(),[arbitrary variable 2],0))
James Baker
Apr 18, 2012 4:12 PM
Oh yeah, DATETRUNC. That's better.
-
4. Re: sum(iif([date]==today(),[arbitrary variable 2],0))
Thank you guys. Going to test shortly and will let you know how it works.
-
5. Re: sum(iif([date]==today(),[arbitrary variable 2],0))
Really appreciate the help. This appeared to do the trick. If you can direct me to any reference material for more advanced formula help for functions like this I would greatly appreciate it. Thank you again.