Query Records by Age

[cunninghamsa]

I want to enable users to view reports of open inquiries by age based on the ReceivedDate field. I would like to create 3 queries to allow them to view records that are between 1-14 days old; 14-28 days old and 28+ days old. I've played around with all kinds of syntax ranging from ''Between ReceivedDate and ReceivedDate+14'' in criteria of the ReceivedDate field to trying SQL view Select * from tbl... where ReceivedDAte > Date()-14. I've gotten all kinds of results from error messages to getting the query to run only to either extract all or none of the records. Can someone assist? I've read a lot of the threads whose topics seemed similar (and attempted to modify to suit my needs) but no luck.

 

[namliam]

For 1-14
where receiveddate >= date()-14
for 15-28
where receiveddate between date() - 15 and date() -28
For 28+
where receiveddate < date() -28

should work...

Regards

 

[dcx693]

cunninghamsa, there is a way to get this all in one query, but only if you need to. You might prefer it to running three separate queries and three separate reports.

The technique is illustrated in this thread: Age bracket query . That query works with a person's age, but you can easily adapt it to working ages of open inquiries.

 

[cunninghamsa]

Age Bracket Query - DB19.zip

Thanks for the reply. I had seen the above and had attempted to open it but got the message that the folder was invalid or corrupted. Unsure if problem is at this end. Am curious to take a look. Would it be possible to send as new attachment?

 

[dcx693]

Strange. Are you sure you downloaded the file as a .zip file? My system appends .php to the file name. I just erase that and get left with db19.zip. Does unzipping the file work? It's in Access 97 format, by the way.

 

[cunninghamsa]

Query by Age

Thank you NamLiam and DCX693. Finally, it worked. I feel like such a nimrod. It appears that the query was not working simply because of the order of the fields. Can't explain it but once I reordered them, all was well. Now, I can move forward and create the reports and automate the preview buttons. Ciao!