Using DateDiff on Multiple Records (1 Viewer)

bufbec

New member
Local time
Today, 08:54
Joined
Aug 20, 2019
Messages
5
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:54
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! Not sure I follow. Are you trying to count the days between records?
 

bufbec

New member
Local time
Today, 08:54
Joined
Aug 20, 2019
Messages
5
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:54
Joined
Oct 29, 2018
Messages
21,358
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?
 

Mark_

Longboard on the internet
Local time
Today, 05:54
Joined
Sep 12, 2017
Messages
2,111
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:54
Joined
May 7, 2009
Messages
19,175
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:54
Joined
May 7, 2009
Messages
19,175
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.
 

bufbec

New member
Local time
Today, 08:54
Joined
Aug 20, 2019
Messages
5
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

New member
Local time
Today, 08:54
Joined
Aug 20, 2019
Messages
5
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:54
Joined
May 7, 2009
Messages
19,175
confused? here is my test db.
you should do the same.
 

Attachments

  • dateSample.zip
    18 KB · Views: 216

bufbec

New member
Local time
Today, 08:54
Joined
Aug 20, 2019
Messages
5
Thank you for your help. I think I've got it now. Thanks again.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:54
Joined
Oct 29, 2018
Messages
21,358
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.
 

Users who are viewing this thread

Top Bottom