INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Query Criteria Based on Text Box values and Dates

Access Query Criteria Based on Text Box values and Dates

(OP)
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 rednose

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

RE: Access Query Criteria Based on Text Box values and Dates

Hi,

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Access Query Criteria Based on Text Box values and Dates

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

RE: Access Query Criteria Based on Text Box values and Dates

(OP)
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 | Expr1: 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 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


RE: Access Query Criteria Based on Text Box values and Dates

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...

CODE --> SQL

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

RE: Access Query Criteria Based on Text Box values and Dates

(OP)
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! glasses And, I'm so excited that a solution is so close!!!

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

CODE

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]
 GROUP BY BoxNo
 HAVING ((((Max(Year([SchedDestruc])))<=[Enter the year for destruction:]) AND (([Records Query].TotalRet)<>"P") AND (([Records Query].DateDestroyed) Is Null)); 



RE: Access Query Criteria Based on Text Box values and Dates

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.

CODE --> sql

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

RE: Access Query Criteria Based on Text Box values and Dates

(OP)
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?

RE: Access Query Criteria Based on Text Box values and Dates

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

RE: Access Query Criteria Based on Text Box values and Dates

(OP)
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!

RE: Access Query Criteria Based on Text Box values and Dates

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

RE: Access Query Criteria Based on Text Box values and Dates

(OP)
Sure.

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

Each box has:
  • a number
  • lists the type of records that are inside,
  • 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).
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!


RE: Access Query Criteria Based on Text Box values and Dates

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

RE: Access Query Criteria Based on Text Box values and Dates

(OP)
Correct!

RE: Access Query Criteria Based on Text Box values and Dates

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

RE: Access Query Criteria Based on Text Box values and Dates

(OP)
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!

RE: Access Query Criteria Based on Text Box values and Dates

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

[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]

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Access Query Criteria Based on Text Box values and Dates

(OP)
Okay, thanks. I'll give it a go.

RE: Access Query Criteria Based on Text Box values and Dates

(OP)
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])).

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	 

Desired output:

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	 

RE: Access Query Criteria Based on Text Box values and Dates

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

RE: Access Query Criteria Based on Text Box values and Dates

Darrylles,
Only entire boxes can be destroyed not records within a box (I believe).
  • 079 has a destroyed date so it's no use putting it in a list to destroy
  • 083 has some records from 2017
  • 096 has some records marked permanent ("p" in TotalRet field)
I look at this more when I have a chance.

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

RE: Access Query Criteria Based on Text Box values and Dates

(OP)
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!

RE: Access Query Criteria Based on Text Box values and Dates

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

RE: Access Query Criteria Based on Text Box values and Dates

How about something like this:

CODE --> sql

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close