CountIIF between a range (not a date)

[Dean1792]

Hi, sorry new to Access apologies if there is a simple answer. moving an Excel file to Access

The requirement:


A record is added to the table with a DateRaised on the form. When that record is closed a date is added to the CloseOutDate. An Age field is calculated.

I want a query that adds all the records between 1-7 Days, 8-14 Days, 15-21 Days, 22-29 Days, 30-59 Days, 60+ Days.

In Excel I used the following

=COUNTIFS('Sheet 1'!V2:V244,">0", 'Sheet 1'!V2:V244,"<8")

In Access I've tried:

1-7 Days: Sum(IIf([Age] Between "0" And "7",1,0))

Thanks in advanced

[ranman256]

Q1, create a sum query , (turn 0n summation) , then make a Count(ID) where between date1 and date2 (per date)
Q2, use Q1 to box your 'range' CountID between 1 and 7

Q3, use Q1 to box your 'range' CountID between 8 and 14

you can put them all together in a union query:
select * from Q2
union

select * from Q3
union
select * from Q4

[pbaldy]

FYI, presuming age is a number, drop the quotes:

Sum(IIf([Age] Between 0 And 7,1,0))

[Dean1792]

I feel dumb... it was literally the quotes that was throwing it off. Thank you

[pbaldy]

Glad you got it working.