get the next record and compared dates

L

lestats

Registered User.
Local time
Yesterday, 19:54
Joined
Apr 12, 2012
Messages
15
Hi,

I have been googling around for this question but I'm still lost.

I have the following table:

case number action date 1 calll 1/1/2014 1 UMAIL 1/1/2013 2 report 3/5/2015 2 umail 4/6/2014 3 final 6/5/2011 3 write 5/5/2010 3 umail 5/7/2009

I would like for each case number to datediff the umail date with the next action date.

For instance for case number 3 it would be "umail"' 5/7/2009 minus "write" 5/5/2010.

How can I do that in a query ?
 
The easiest way is via a DMin (https://www.techonthenet.com/access/functions/domain/dmin.php) inside your DateDiff.

What you do is use the DMin to get the action date of the lowest action date that is higher than your current record for the same case. Roughly, it would look like this:

DaysToNextAction: DateDiff("d", ActionDate, DMin("[ActionDate]", "YourTableName", "[CaseNumber]=" & [CaseNumber] & " AND [ActionDate]>" & [ActionDate]))
 
I have uploaded a sample of the file to provide a better example. I don't know why in my initial post the table didn't format correctly. I'm bad with PHP I guess.

I don't think the DMIN will work as I have other transactions before the UMAIL action - see case number 2 and 3 . Sorry for the confusion.

I will look at Allen's website to see if I can figure out something from it.

Thx for your quick replies.

Lestats
 

Attachments

DMin is the way to go. In the criteria, along with the CaseNumber and Date I laid out, you would use the action as well.
 
Thx Plog for your reply.

In the query, the expression field returns the value error. I will try to figure out why. I think we are close to something.
 
It works in the file I uploaded but whenI try it in my real database it doesn't work. It might be just a little thing that I have to find out. Thx.
 
See the attachment to see the error I receive in my real database when I run the query. I have tried to fix it but can't find the solution.

In SQL the expression I have is :


SELECT DateDiff("d",[Action_Date_Formatted],DMin("[Action_Date_Formatted]","umail_all_transactions","[Case_NO]=" & [Case_NO] & " AND [Action_Date_Formatted]>" & [Action_Date_Formatted])) AS Expr1
FROM umail_all_transactions;
 

Attachments

  • Capture.JPG
    Capture.JPG
    22.9 KB · Views: 125
Last edited:
I think you need to put # around the date on the right side of the comparison.
 
I think it's because my case_no field is in text. The case number field in the database its format is i.e. 000021254L2. That's why it returns a error I believe.
 
Both actually:

DMin("[Action_Date_Formatted]","umail_all_transactions","[Case_NO]='" & [Case_NO] & "' AND [Action_Date_Formatted]>#" & [Action_Date_Formatted] & "#")
 
Hi,

The Solution that works for we is the following. Thx for you help much appreciated. I have learned a lot.

SELECT T.casenumber, T.actionDate, T.NextDate, T.action, DateDiff("d",actionDate,NextDate) AS Expr1
FROM (SELECT casenumber, actionDate, action, ( SELECT MIN(actionDate) FROM 1 T2 WHERE T2.casenumber = T1.casenumber AND T2.actionDate > T1.actionDate ) AS NextDate FROM 1 AS T1) AS T
ORDER BY T.casenumber, T.actionDate;
 
You must log in or register to reply here.

Similar threads

K
Adding and Deleting records in a junction table.
Replies
3
Views
286
KitaYama
K
H
Solved Case Statement to get value in Access Query
Replies
15
Views
1,476
Pat Hartman
P
A
Tab Index of fields NOT reacting in Order
Replies
5
Views
392
The_Doc_Man
The_Doc_Man
S
Anyone can help on this ? Total Date In Access
Replies
2
Views
454
Pat Hartman
P
I
Create and training record database
Replies
8
Views
767
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom