Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-20-2019, 08:10 AM   #1
bufbec
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
bufbec is on a distinguished road
Using DateDiff on Multiple Records

New to the forum here, but I have been working with Access for some time. Using Office 365 Pro Plus. I have a situation I cannot figure out. I have a table that contains dates of service, and I need to drop any date of service that is <14 days apart. A sample of some of the dates of service, and number of days to next:
10/8/2018 7 days
10/15/2018 10 Days
10/25/2018 20 Days
11/14/2018

So, from this batch I only want to drop the 10/15/2018 date. 10/8/2018 to 10/25/2018 is 17 days, so that date is ok. But of course it is reading the 10/15/2018 prior date so it looks like it should be dropped. I am using the DateDiff function to calculate all the days, but I am not sure what to do to keep in the 10/25/2018 date. I do not know how to use VBA, so if that is the only solution, I will need some hand holding to make it work. Thanks for your assistance.

bufbec is offline   Reply With Quote
Old 08-20-2019, 08:14 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,117
Thanks: 45
Thanked 964 Times in 946 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Using DateDiff on Multiple Records

Hi. Welcome to AWF! Not sure I follow. Are you trying to count the days between records?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 08-20-2019, 08:16 AM   #3
bufbec
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
bufbec is on a distinguished road
Re: Using DateDiff on Multiple Records

Yes, I am counting the difference between the dates. Anything greater than 14 days is ok, anything less than 14 days I need to drop. Sorry if i wasn't clear. Thanks.

bufbec is offline   Reply With Quote
Old 08-20-2019, 08:28 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,117
Thanks: 45
Thanked 964 Times in 946 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Using DateDiff on Multiple Records

Quote:
Originally Posted by bufbec View Post
Yes, I am counting the difference between the dates. Anything greater than 14 days is ok, anything less than 14 days I need to drop. Sorry if i wasn't clear. Thanks.
Hi. In your sample data, the first service date is 10/8 and the second service date is 10/15. The difference between the two dates is only 7 days. So, why does 10/8 get to stay?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 08-20-2019, 08:40 AM   #5
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,001
Thanks: 20
Thanked 376 Times in 369 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Using DateDiff on Multiple Records

Where is the first date saved? Is it something you can reference?

If not, you'll need to figure out how you want to hold on to that value, otherwise you have no way to calculate it per entry.
Mark_ is offline   Reply With Quote
Old 08-20-2019, 08:48 AM   #6
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,968
Thanks: 64
Thanked 2,537 Times in 2,437 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Using DateDiff on Multiple Records

you need to query, the one you already have.
the other one filters only the first query where < 14 and it is not the first or the last record:
Code:
first query:

SELECT yourTable.dateField, 
  (select top 1 t1.datefield from yourTable as t1 where t1.datefield>yourTable.datefield order by t1.datefield) AS Expr1,
   DateDiff("d",[datefield],Nz([Expr1],#12/31/9999#)) AS Expr2
FROM yourTable;

result:

dateField	          Expr1	        Expr2
08-Oct-19	          15-Oct-19	7
15-Oct-19	          25-Oct-19	10
25-Oct-19	          14-Nov-19	20
14-Nov-19		                       2914682
the "final" query:
Code:
SELECT yourFirstQuery.dateField, yourFirstQuery.Expr1, 
yourFirstQuery.Expr2 FROM yourFirstQuery 
WHERE (((yourFirstQuery.Expr2)>14)) OR 
(((yourFirstQuery.dateField)=(select min(datefield) from datetable)));

result:

dateField	                Expr1	    Expr2
08-Oct-19	                15-Oct-19	     7
25-Oct-19	                14-Nov-19	     20
14-Nov-19		                             2914682
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-20-2019, 09:45 AM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,968
Thanks: 64
Thanked 2,537 Times in 2,437 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Using DateDiff on Multiple Records

replace "datetable" with your tablename.

(((yourFirstQuery.dateField)=(select min(datefield) from yourTableName)));

you did not mention any table name or any field name so I made a table for myself with the data you have.
all tablenames and fieldnames in the query are placeholders.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-20-2019, 09:55 AM   #8
bufbec
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
bufbec is on a distinguished road
Re: Using DateDiff on Multiple Records

arnelgp - Query 1 - didn't quite work, I kept getting an error 'At most one record can be returned by this subquery'. So I changed the '>' to a '<', and it seems to work. But I am getting weird results.
DOS.............|Expr1.........|Expr2
10/8/2018 ....|10/5/2018...|-3
10/15/2018...|10/5/2018...|-10
10/25/2018...|10/5/2018...|-20
11/14/2018...|10/5/2018...|-40
12/11/2018...|10/5/2018...|-67

I had to put the periods and pipes in there to keep the columns separated. So, I don't know where the data for Expr1 comes from. And then, Expr2 is subtracting Expr1 from the DOS, so it is not giving the expected result. For example, I would expect DOS 10/25/2018 to have a value of 10, because it is 10 days from the prior date of 10/15/2018.

I also tried your query 2. You are using query 1 as the data source, but the very last phrase states 'from datetable'. I put query 1 in its place and get a single row of data. So I'm not sure what goes there.

Thanks for all your help.
bufbec is offline   Reply With Quote
Old 08-20-2019, 09:59 AM   #9
bufbec
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
bufbec is on a distinguished road
Re: Using DateDiff on Multiple Records

I'm getting things out of order here. Sorry, I'm new and trying! I had to delete my comment in between because it wasn't quite right.

your answer of
replace "datetable" with your tablename.
doesn't make sense, since I am using query 1 as the data source, not a table.

And then what I just posted above still shows what is not working for me. thanks.
bufbec is offline   Reply With Quote
Old 08-20-2019, 10:14 AM   #10
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,968
Thanks: 64
Thanked 2,537 Times in 2,437 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Using DateDiff on Multiple Records

confused? here is my test db.
you should do the same.
Attached Files
File Type: zip dateSample.zip (18.0 KB, 6 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
bufbec (08-23-2019)
Old 08-23-2019, 12:13 PM   #11
bufbec
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
bufbec is on a distinguished road
Re: Using DateDiff on Multiple Records

Thank you for your help. I think I've got it now. Thanks again.
bufbec is offline   Reply With Quote
Old 08-23-2019, 12:15 PM   #12
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,117
Thanks: 45
Thanked 964 Times in 946 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Using DateDiff on Multiple Records

Quote:
Originally Posted by bufbec View Post
Thank you for your help. I think I've got it now. Thanks again.
Hi. Congratulation! Glad to hear you got it sorted out. Good luck with your project.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Selecting records for a recordset using Datediff MarionD Modules & VBA 6 04-15-2014 07:10 AM
=DateDiff( between multiple dates????? weeblebiker Queries 36 04-10-2013 06:15 PM
DateDiff between 2 records davea300 Queries 1 09-05-2008 05:42 AM
[SOLVED] How can I calculate datediff between 2 records? ccIces Modules & VBA 2 08-12-2005 06:15 PM
DateDiff with Multiple Responses donbettis Forms 3 01-07-2002 03:17 PM




All times are GMT -8. The time now is 01:27 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World