Date Count and Grouping

  • Thread starter Thread starter palvarez64
  • Start date Start date
P

palvarez64

Hello, I need help in resolving my dilemma,

In the query design I have to fields [OpenDate] and [CloseDate], and the
third field I have a formula "Length of Days:([CloseDate]-[OpenDate]+1)" and
I'm getting results, but I also need to do a grouping of " 0 - 30", " 31 -
60", " 61 - 90" and ">90".

Can somebody help?
Thanks in advance, Pedro
 
Use an expression like the following to calculate the value.

IIF(CloseDate - OpenDate <=30,"0-30", IIF(CloseDate
-OpenDate<=60,"31-60",
IIF(CloseDate-OpenDate<=90,"61-90",IIF(CloseDate-OpenDate>90,">90",Null))))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top