Solved DMax and Days between Dates

[desertbird]

Hello All,

I am having trouble with a query where I want to calculate the number of days between dates where I have a UserID, JOB, and Date.

To do that all I need is the previous Date "PrevDate" each UserID was in a JOB.

I do not have any issues writing a DMax to do this when I only have one UserID in one JOB. I really got lost on what to add to the DMax criteria when I expanded my Data to all my UserIDs working all JOBs.

I really appreciate any help in my DMax function that will show "PrevDate" from the Data example below. Not too concerned about how to get "ElpsDays".

Here is the DMax function that works when I only had one UserID and one JOB: DMax("Date","Data","Date<#" & [Date] & "#")

Here is what my Data looks like with the desired result in the "PrevDate" column:

UserID	JOB	Date	PrevDate	ElpsDays
argrol	RECEIVING DOMESTIC	5/10/2022
argrol	RECEIVING DOMESTIC	5/16/2022	5/10/2022	6​
argrol	RECEIVING DOMESTIC	5/18/2022	5/16/2022	2​
argrol	RECEIVING DOMESTIC	5/19/2022	5/18/2022	1​
argrol	RECEIVING DOMESTIC	5/25/2022	5/19/2022	6​
argrol	RECEIVING IMPORT	5/26/2022
argrol	RECEIVING IMPORT	5/29/2022	5/26/2022	3​
argrol	RECEIVING IMPORT	5/31/2022	5/29/2022	2​
allenjaq	PICKING MODULE	5/9/2022
allenjaq	PICKING MODULE	5/10/2022	5/9/2022	1​
allenjaq	PICKING MODULE	5/20/2022	5/10/2022	10​
allenjaq	PICKING MODULE	5/21/2022	5/20/2022	1​
allenjaq	PICKING MODULE	5/22/2022	5/21/2022	1​
allenjaq	PICKING MODULE	5/25/2022	5/22/2022	3​

Thank you in advance!!!!

 

[Guus2005]

There is a function called DateDiff which you can use for this purpose

HTH

 

[desertbird]

There is a function called DateDiff which you can use for this purpose

HTH

Hello, thanks for the insight, yes there is DateDiff. That is what I use to calculate "Elps Days" in my example and don't need help with.

I need help with figuring out the "PrevDate" Field....

 

[plog]

DMax("Date","Data","Date<#" & [Date] & "#")

First, I'm surprised the above works. 'Date' is a reserved word and should not be used as a name in a database. Instead you should prefix it with what the date represents (JobDate, StartDate, etc). When you use a reserved word you have to surround it by brackets when you reference it. I'm surprised the 2 instances without brackets are working.

When you need to add multiple citeria that all must be true you seperate them with an AND:

DMax("[Date]","Data","[Date]<#" & [Date] & "# AND UserID='" & [UserID] & "' AND Job='" & [Job] & ''")

 

[Guus2005]

sorry, didn't read the whole question...

you can join the table with itself and in the join select the values smaller than Date
Something like this:

select t1.date, t2.date as PrevDate from Table t1 join Table t2 on t1.date < t2.date

There should also be a group by involved. This is not a solution. More an idea on where to look for a solution.
Post a sample database for a better answer.

HTH

 

[desertbird]

DMax("Date","Data","Date<#" & [Date] & "#")

First, I'm surprised the above works. 'Date' is a reserved word and should not be used as a name in a database. Instead you should prefix it with what the date represents (JobDate, StartDate, etc). When you use a reserved word you have to surround it by brackets when you reference it. I'm surprised the 2 instances without brackets are working.

When you need to add multiple citeria that all must be true you seperate them with an AND:

DMax("[Date]","Data","[Date]<#" & [Date] & "# AND UserID='" & [UserID] & "' AND Job='" & [Job] & ''")

Thank you so Much Plog!!!!

Yes, sorry about the Date, it's actually named CI_Date.

I tried your expresssion as:

DMax("[CI_Date]","Data","[CI_Date]<#" & [CI_Date] & "# AND UserID='" & [UserID] & "' AND Job='" & [Job] & ''")

but I get and don't know what to change:

 

[desertbird]

sorry, didn't read the whole question...

you can join the table with itself and in the join select the values smaller than Date
Something like this:

select t1.date, t2.date as PrevDate from Table t1 join Table t2 on t1.date < t2.date

There should also be a group by involved. This is not a solution. More an idea on where to look for a solution.
Post a sample database for a better answer.

HTH

I tried that route in the beginning, but it doesn't work too well. I then found out how to use DMax, but get lost on the additional criteria for more then one user and job. Manly around where the double quotes and single quotes go and when to [ ] a field etc.

 

[plog]

The second to last single quote should be a double quote. It's best if you just copy the below code, it shoudl work:

DMax("[CI_Date]","Data","[CI_Date]<#" & [CI_Date] & "# AND UserID='" & [UserID] & "' AND Job='" & [Job] & "'")

 

[Gasman]

Anything more than one item of criteria, I tend to put it all into a string variable, then debug.print it until correct, then use that in the function.

 

[desertbird]

The second to last single quote should be a double quote. It's best if you just copy the below code, it shoudl work:

DMax("[CI_Date]","Data","[CI_Date]<#" & [CI_Date] & "# AND UserID='" & [UserID] & "' AND Job='" & [Job] & "'")

That works!!!!!!! WOOOOOOO HOOOOOOOOO!!!!!

I owe you a case of beer, a bottle of wine or what ever your favorite drink is. I have been trying to get this figured out for a week!

THANK YOU SO MUCH!!!!!!

 

[desertbird]

Anything more than one item of criteria, I tend to put it all into a string variable, then debug.print it until correct, then use that in the function.

That is good to know, Thank you! I did see some suggestion on using debug.print early on trying to figure out my Dmax issue, but am lost on what and where. Would you happen to have a good URL that explains it for dummies like me?

 

[Gasman]

You add the debug.print to your code, then look in the immediate window (ctrl+g) for the output.
Google 'breakpoints in access' where you can pause code and inspect values. Then use F8 to move line by line, seeing what the code actually does, not what you think it does.

Debugging tips in my signature and plenty of videos of access on youtube.