Access Query Criteria Based on Text Box values and Dates

[Dawnit]

Hi,
I am trying to create a query that does not return any records with the same BoxNo (box number) if those records also have a scheduled destruction year that is <=Year(Now())

I’ve spent almost 16 hours trying so many different IIf statements and expressions that I’m so confused. So, I hope if I show you an example of the records I need to filter and another example of the results I need that you can start me in the right direction. I would so grateful for your help

Example of boxed record fields and data:

BoxNo | Function | SchedDestruc | Year[SchedDestruc]
(text box)

003-81 | Daily Deposit | 12/31/15 | 2015
003-81 | Mo. Deposit | 12/31/15 | 2015
003-81 | Mo. Deposit | 12/31/14 | 2014
003-82 | Daily Deposit | 12/31/16 | 2016
003-82 | Mo.Deposit | 12/31/16 | 2016
003-83 | Daily Deposit | 12/31/16 | 2016
003-83 | Mo. Deposit | 12/31/17 | 2017
003-83 | Daily Deposit | 12/31/14 | 2014

Example of what I need:

BoxNo | Function | SchedDestruc | Year[SchedDestruc]

003-81 | Daily Deposit | 12/31/15 | 2015
003-81 | Mo. Deposit | 12/31/15 | 2015
003-81 | Mo. Deposit | 12/31/14 | 2014
003-82 | Daily Deposit | 12/31/16 | 2016
003-82 | Mo.Deposit | 12/31/16 | 2016

 

[dhookom]

Darrylles,
Only entire boxes can be destroyed not records within a box (I believe).
[ul]
[li]079 has a destroyed date so it's no use putting it in a list to destroy[/li]
[li]083 has some records from 2017[/li]
[li]096 has some records marked permanent ("p" in TotalRet field)[/li]
[/ul]

I look at this more when I have a chance.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Dawnit]

Hi Darrylles,

dhookom is totally right. We only want to destroy a box of records once all of the records inside have met their retention. The boxes are supper heavy and many are on a shelf 10 feet high, so it would a lot of work to fish out the items that could be destroyed sooner than later.

Thank you!

 

[Dawnit]

How's it going dhookom? Sorry to bug, just wondering if there's any chance you'll have a solution for me soon. Thanks!

 

[dhookom]

How about something like this:

SELECT [Records Query].BoxNo, [Records Query].Description, [Records Query].SchedDestruc, 
[Records Query].DateDestroyed, [Records Query].TotalRet
FROM [Records Query]
WHERE [Records Query].BoxNo In 
  (SELECT BoxNo
   FROM [Records Query]
   WHERE  [Records Query].TotalRet<>"P" AND [Records Query].DateDestroyed Is Null
   GROUP BY BoxNo
   HAVING Max(Year([SchedDestruc]))<=[Enter the year for destruction:] )
AND [Records Query].BoxNo NOT IN (SELECT BoxNo WHERE TotalRet ="P");

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016