-
1. Re: sum(iif([date]==today(),[arbitrary variable 2],0))
James BakerApr 18, 2012 4:03 PM (in response to justin.goodrich)
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))
Richard Leeke Apr 18, 2012 4:07 PM (in response to James Baker)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 BakerApr 18, 2012 4:12 PM (in response to Richard Leeke)
Oh yeah, DATETRUNC. That's better.
-
4. Re: sum(iif([date]==today(),[arbitrary variable 2],0))
justin.goodrich Apr 19, 2012 9:03 AM (in response to James Baker)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))
justin.goodrich Apr 20, 2012 6:22 AM (in response to James Baker)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.