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

 

[SkipVought]

Hi,

Are you stating that BoxNo 003-83 is not returned and if so, on what criteria?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

Do you really have a field name with []s in it?
Why are some 2016 records selected and others aren't?
Do you realize if you are storing the date, you don't have to (shouldn't) store the year?

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

 

[Dawnit]

Hi,

Skip and dhookom, thanks for giving this a try. I'm so sorry if I was confusing you!

I should say that I'm working with a query in design view not SQL or VBA.
Criteria for the field Expr1: Year[SchedDestruc] is <=[Enter the year for destruction:]

When I enter a parameter value of 2016, all boxes with records less than 2017 are returned, except the one with SchedDestruc date 12/31/17. But that's not what I'm after.
I'm using 003-83 as an example to say: if there is a box that contains a mixture of records that also have a scheduled destruction date with a year that is same or older than my parameter value (year), I don't want any boxes with the same BoxNo returned.

Re: dhookom's comment about storing the year, I'm not storing the year, just using this function: Year[SchedDestruc] in an expression field (Expr1) to work with my parameter value.

I hope I've explained things clearly. More and corrected examples below.
Thanks ever so much for your help!!

Example of boxed record fields and data:

BoxNo | Function | SchedDestruc | [highlight #FCE94F]Expr1: Year([SchedDestruc])[/highlight]
(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 get with the current criteria of <=[Enter the year for destruction:]

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
003-83 | Daily Deposit | 12/31/16 | 2016
003-83 | Daily Deposit | 12/31/14 | 2014

 

[dhookom]

That is a better explanation. It would have really helped if you would have started with something like:

I have storage boxes with items that have varying destruction dates. So a single box could have dates from multiple years. I am trying to query...

SELECT Dawnit.*, Dawnit.BoxNo
FROM Dawnit
WHERE Dawnit.BoxNo In 
(SELECT BoxNo
 FROM Dawnit
 GROUP BY BoxNo
 HAVING Max(Year(SchedDestruc))<=[Enter the year for destruction:]);

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

 

[Dawnit]

I'm so sorry dhookom, I get your point about how explain. I guess my brain was so immersed in the details that I couldn't come at it from a simple point of view.

Your solution works like a charm! ...except it asks me for the year for the destruction twice. Also, I can't seem to add criteria to two new fields: DateDestroyed and TotalRet. I tried working it out in design view and SQL, but it's not working. I want to exclude records that have a date in the DateDestoryed field, and that don't have a total retention of "P" for permanent. I hope I said that simply! And, I'm so excited that a solution is so close!!!

Here's your code with my revisions and the error I get:

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

 

[dhookom]

Try the following. Since the TotalRet and Datedestroyed are filtering on the non-aggregated values, you place them in the WHERE clause not the HAVING clause. You might have included some []s or ()s that were not required.

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:] );

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

 

[Dawnit]

Hi dhookom,

Gave that a try, but still having troubles.

1. The [Records Query].TotalRet<>"P" part does not work. A lot of records are returned with a TotalRet of "P"
2. I tried things with and without either of the new criteria in the second Where clause, but I don't get all of the records returned that I should from the first bit of code you gave me. Nine boxes are left out of the query results, and I can't figure out why. None of the records in those 9 boxes have a SchecDestruc > the parameter value (2016 in this example); none have a TotalRet of "P"; and all have a null DateDestroyed. I checked the data in the other fields of the query, and they are consistent with data in the boxes that are and should be returned in the query results.

What do you think is going on?

 

[dhookom]

Do you want the <>"P" applied after the Max() year? Also, is it possible the TotalRet value is NULL?

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

 

[Dawnit]

Hi dhookom,

I don't know why my reply to your last post is missing. I posted it on the same day as you did. So Sorry. Anyway, let me reply again and say I hope you'll stick with me for a solution.

I'm not sure how applying the <>"P" after the Max()Year would affect things, since I don't completely understand the SQL statement. Re: the TotalRet value, all records must have and do have some sort of retention (e.g., P, AA + 7, C + 5).

Thanks so much!

 

[dhookom]

Dawnit,
Please describe the actual process (in order) you would go through in selecting records/boxes to destroy.

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

 

[Dawnit]

Sure.

If I didn't have a database, I'd go into storage and look at every box of records.

Each box has:
[ul]
[li]a number[/li]
[li]lists the type of records that are inside,[/li]
[li]and includes either a P for permanent or a SchedDestruc date, for each type of record. (Unfortunately, permanent and non-permanent records were boxed together in the past. Now we box them separately. Who know what'll be done in the future).[/li][/ul]

If the box does not have any records that are permanent (P) and/or a SchedDestruc date beyond the max year that's scheduled for destruction (currently 2016), I would mark the box with a big red "X." If the box has P records and/or records that aren't scheduled for destruction, I'd pass it by.

Extra relevant notes: Sometimes we hold records longer than their retention, and historically, designated retentions may change. In my database, purged and unpurged records are in the same table. The purged have a destruction date, but that field is null for unpurged records.

I hope this helps!

 

[dhookom]

So the big red X are the records you want returned by your query and the box doesn't include any permanent records?

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

 

[Dawnit]

Correct!

 

[dhookom]

Please provide your data with ALL of the significant columns/fields and the desired output. Make sure you have a variety of data to confirm the desired functionality. Also, use the PRE TGML to format your data.

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

 

[Dawnit]

What is PRE TGML? And, are you asking for a stripped down version of my database? I'm also not sure how to provide the desired output if I can't get a query to do it.

Thank you again!

 

[Andrzejek]

To format your data and preserve the spacing, use PRE tgml tag:

[ignore][pre][/ignore]
[pre]
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[/pre]
[ignore][/pre][/ignore]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Dawnit]

Okay, thanks. I'll give it a go.

 

[Dawnit]

Okay, I hope I did this right!

The first bit is my data with significant columns/fields. The second set of data is the desired output if I were to enter a parameter of 2016 for Max(Year([SchedDestruc])).

[pre]
BoxID BoxNo Func Description DateFrom DateTo SchedDestruc TotalRet DateDestroyed
003 029 UB Liens 07/01/2001 06/30/2002 P
003 073 UB Daily Deposit 02/19/2008 05/31/2008 12/31/15 AA + 7
003 079 UB Daily Deposit 07/01/2005 10/30/2005 12/31/13 AA + 7 12/31/2014
003 083 UB Daily Deposit 10/01/2008 01/31/2009 12/31/16 AA + 7
003 083 UB Cash Receipts 07/01/2009 06/30/2010 12/31/17 AA + 7
003 083 UB Payments 07/01/2009 06/30/2010 12/31/14 C + 4
003 089 UB Daily Deposit 10/21/2009 02/03/2010 12/31/17 AA + 7
003 096 UB Cash Receipts 08/01/2011 11/30/2011 12/31/18 AA + 7
003 097 UB Daily Deposit 12/01/2011 02/29/2012 12/31/19 AA + 7
003 102 UB Daily Deposit 01/28/2013 03/31/2013 12/31/20 AA + 7
004 067 P/R W-2 Forms 06/30/2004 06/24/2005 12/31/11 AA + 6
004 071 P/R Time Sheets 01/16/2005 07/03/2005 12/31/12 AA + 7
004 096 P/R Emp Terminated 01/01/1999 12/31/2000 12/31/07 T + 7
004 096 P/R Leave Report 01/01/1999 12/31/2000 P
004 107 P/R Payroll Reports 07/01/1995 06/30/2001 12/31/07 AA + 6
004 107 P/R Emp Terminated 01/01/2007 12/31/2007 12/31/14 T + 7 12/31/2014
005 125 G/A AR 07/01/2009 06/30/2010 12/31/17 AA + 7
005 126 G/A Bank Statements 07/01/2007 06/30/2008 12/31/15 AA + 7
005 126 G/A AR 07/01/2007 06/30/2015 12/31/22 AA + 7 [/pre]

Desired output:

[pre]
BoxID BoxNo Func Description DateFrom DateTo SchedDestruc TotalRet DateDestroyed
003 029 UB Liens 07/01/2001 06/30/2002 P
003 073 UB Daily Deposit 02/19/2008 05/31/2008 12/31/15 AA + 7
003 079 UB Daily Deposit 07/01/2005 10/30/2005 12/31/13 AA + 7 12/31/2014
003 083 UB Daily Deposit 10/01/2008 01/31/2009 12/31/16 AA + 7
003 083 UB Cash Receipts 07/01/2009 06/30/2010 12/31/17 AA + 7
003 083 UB Payments 07/01/2009 06/30/2010 12/31/14 C + 4
003 089 UB Daily Deposit 10/21/2009 02/03/2010 12/31/17 AA + 7
003 096 UB Cash Receipts 08/01/2011 11/30/2011 12/31/18 AA + 7
003 097 UB Daily Deposit 12/01/2011 02/29/2012 12/31/19 AA + 7
003 102 UB Daily Deposit 01/28/2013 03/31/2013 12/31/20 AA + 7
004 067 P/R W-2 Forms 06/30/2004 06/24/2005 12/31/11 AA + 6
004 071 P/R Time Sheets 01/16/2005 07/03/2005 12/31/12 AA + 7
004 096 P/R Emp Terminated 01/01/1999 12/31/2000 12/31/07 T + 7
004 096 P/R Leave Report 01/01/1999 12/31/2000 P
004 107 P/R Payroll Reports 07/01/1995 06/30/2001 12/31/07 AA + 6
004 107 P/R Emp Terminated 01/01/2007 12/31/2007 12/31/14 T + 7 12/31/2014
005 125 G/A AR 07/01/2009 06/30/2010 12/31/17 AA + 7
005 126 G/A Bank Statements 07/01/2007 06/30/2008 12/31/15 AA + 7
005 126 G/A AR 07/01/2007 06/30/2015 12/31/22 AA + 7 [/pre]

 

[Darrylles]

So, why don't you want to see:

Box no's: 079, 12/31/13
083, 12/31/16
083, 12/31/14
096, 12/31/07

All of these are older or = 2016.

ATB,

D