Count No of Records between Dates

[MattioMatt]

Hi,

I'd like to be able to count the number of records between two sets of dynamic dates using an expression as I want to display this information in an unbound textbox on a form.

I have a column on a continuous form that shows "FirstDetected" as a date. I'd like to count the number of records that fall into the 4 following criteria.
The date shows vulnerabilities and when they were first picked up, I'm aiming to count records that are older than the following first criteria, how can I do that?

0-60 days
61-120 days
121-180 days
180+ days

I've tried the following as the control source on the textbox

=Count([FirstDetected] BETWEEN Date() AND >=Date()-60,1,0)

=DCount("*","qryUnionVulnAssets","[FirstDetected] Between =Date() & >=Date()-60 AND [AssetID] = " & [txtAssetID])

I'm probably doing something really silly, I can't seem to work it out.

 

[Ranman256]

Make Q0 to read the dates off the form:
Select iD, DateDiff("d",[date],date()) as DAYS from table where [date] between foms!MyForm!txtStrtDate and foms!MyForm!txtEndDate

Then make 4 queries for the Union query to count them,
Select iif(Days >=0 and days< 61,1,0) as "0 to 60",0,0,0 from Q0
Select 0,iif(Days >60 and days< 121,1,0) as "61 to 120",0,0 from Q0
Select 0,0,iif(Days >120 and days< 18,1,0) as "121 to 180",0 from Q0
Select 0,0,0iif(Days >180 ,1,0) as "181 +" from Q0

 

[MattioMatt]

Thanks!

Although I didn't think it was possible to have a SQL statement assign to a text box?