calculating difference between dates in 2 rows

[SChua]

Hi,
I have a list of client activity - client name, loan ID and loan date. I would like to create a new field that shows the number of days between one loan and the next. If I was doing it in excel, I would need to sort the data by client name and loan date and then calculate the number of days between the loan date of one loan and the loan date of the loan immediately prior to this.
Is there a way of doing this in Access?
Thanks in advance.

 

[plog]

Yes you would use a DMax(http://www.techonthenet.com/access/functions/numeric/max.php) function call inside a DateDiff (http://www.techonthenet.com/access/functions/date/datediff.php) function call.

The Dmax will return the highest date prior to the one you are working with and the DateDiff will calculated the difference between the two. Check out the links I provided and then give it a shot. If you have trouble post back here with your code, any error messages and/or the actual results vs. expected results.

 

[SChua]

Thanks very much for your pointers. I tried them out. DateDiff is easy enough, but I am not so sure about how to use DMax.
See attached for my sample. I have tried out Expr1 (datediff) and Expr2 (DMax) in Query1. The plan is to embed Dmax into Datediff.
The data has each client uniquely identified by ClientID. Each client may have 1 or more loans (identified by LoanID). Each loan is associated with a disbursement date.
I need to get the difference between each loan date by client.Would you please correct my DMax expression in Expr2.

Thanks again in advance.

 

[plog]

This thing is so complex its best to break it out into a custom function inside a module. So, paste the below code into a module:

Public Function getLastLoanDays(id, dd)
    ' gets days since last loan for a client id (id) and current disbursement date (dd)
 
ret = -1
    ' default value is negative to show no prior loan
    
str_Criteria = "[ClientID]='" & id & "' AND [DisbursementDate]<#" & dd & "#"
    ' criteria to use for DCount and DMax function calls

If DCount("[ClientID]", "NewLoans", str_Criteria) Then
    ' if client had loans prior to current one, calculates days since last loan
    date_LastLoan = DMax("[DisbursementDate]", "NewLoans", str_Criteria)
    ret = DateDiff("d", date_LastLoan, dd)
End If
 
getLastLoanDays = ret
 
End Function

Then to use it in a query you would do this:

DaysSinceLastLoan: getLastLoanDays([ClientID],[DisbursementDate])

It returns -1 for the first loan, edit the first line of the function to change that if necessary.

 

[SChua]

I followed your instructions and it works. I changed the ret (default) to "" and that worked to. Thanks a million.
One further question - if I were to have the module in an access database with other tables, would I need to include the table name before the field name in the module?

Swee

 

[plog]

if I were to have the module in an access database with other tables, would I need to include the table name before the field name in the module?

I'm not entirely clear what you are asking. I wrote that function to work with only the table named "NewLoans" that has fields "ClientID" and "DisbursementDate". Those 3 values are hard coded, if you want it to work for other tables/fields you would need to edit the function. Was that your question?

 

[SChua]

What I meant was that I had extracted the NewLoans table in the example but it actually is in a database with other tables. So I assume that in the module, where the code says, for example, "[ClientID]", that this should be replaced by "[NewLoans]![ClientID]". I tried this and it works - not sure if the changes made were redundant or not.

One (hopefully last) issue - when I include a 'make table' component into the query (ie, so that the output is in a new table) the query seems to take forever to run - its been running for over 7 mins now and still not complete (compared to less than one without the "make table" component). Is this normal?

 

[plog]

[NewLoans]![ClientID] is redundant. Slowness is normal. Queries like this always take a lot of resources/time because for every record in the table it has to look at every other record in the table.

 

[SChua]

Thanks very much for your help!

 

[SChua]

Hi,
May I ask a couple another question on this?

Does the data have to be pre-sorted before the query is run? The reason why I ask is that I can't seem to replicate some results from the query and am wondering if its due to how the data was sorted or the logic in how the code is written is executed. I am attaching an extract in excel for 1 particular client with the results from the query as well as my one calculation as to what it should be.

The code that I used is as follows (slight modifications from yours based on variable names):

Public Function getLastLoanDays(id, dd)
' gets days since last loan for a client id (id) and current disbursement date (dd)

ret = ""
' default value is negative to show no prior loan

str_Criteria = "[TRN]='" & id & "' AND [DisbursementDate]<#" & dd & "#"
'str_Criteria = "[NewLoans]![TRN]='" & id & "' AND [NewLoans]![DisbursementDate]<#" & dd & "#"
' criteria to use for DCount and DMax function calls
If DCount("[TRN]", "NewLoans", str_Criteria) Then
' if client had loans prior to current one, calculates days since last loan
date_LastLoan = DMax("[ClosingMonth]", "NewLoans", str_Criteria)
ret = DateDiff("d", date_LastLoan, dd)
End If

getLastLoanDays = ret

End Function

 

[plog]

Does the data have to be pre-sorted before the query is run?

The code I provided doesn't need the data ordered. It finds the record with the immediate disbursement date prior to the current record for that ClientID.

I think the discrepancy was created when you changed this line:

date_LastLoan = DMax("[DisbursementDate]", "NewLoans", str_Criteria)

to this line:

date_LastLoan = DMax("[ClosingMonth]", "NewLoans", str_Criteria)

Change that back and it should match yours.

 

[SChua]

I made the change to the code as I was trying to get the difference between closing date of previous loan and disbursement date of current loan (once it identified the record with the immediate disbursement date) ... is there a way of getting this?
Thanks.

 

[plog]

This should do it:

Public Function getLastLoanDays(id, dd) As Integer
    ' gets days since last loan for a client id (id) and current disbursement date (dd)
 
ret = -1
    ' default value is negative to show no prior loan
    
str_Criteria = "[ClientID]='" & id & "' AND [DisbursementDate]<#" & dd & "#"
    ' criteria to use for DCount and DMax function calls

If DCount("[ClientID]", "NewLoans", str_Criteria) Then
    ' if client had loans prior to current one, calculates days since last loan
    date_LastLoan = DMax("[DisbursementDate]", "NewLoans", str_Criteria)
    date_LastLoan = DLookup("[ClosingMonth]", "NewLoans", "[ClientID]='" & id & "' AND [DisbursementDate]=#" & date_LastLoan & "#")
    ret = DateDiff("d", date_LastLoan, dd)
End If
 
getLastLoanDays = ret
 
End Function

 

[SChua]

Thanks very much. It works perfectly!

 

[dsthomson]

Hi -
Thanks for this post and responses - they have been very helpful.

I have attempted to apply the same procedure to a tool rental database but the computations are not working. I am just getting a 0 result to every record.

Can anyone show me corrections to my code:

Public Function getLastTransferDays(ID, dd)
' gets days since last loan for a client id (id) and current disbursement date (dd)
ret = 0
' default value is negative to show no prior loan

str_Criteria = "[ToolNum]=" & ID & " AND [TransDate]<#" & dd & "#"
' criteria to use for DCount and DMax function calls
If DCount("[ToolNum]", "Tools", str_Criteria) Then
' if client had loans prior to current one, calculates days since last loan
date_LastTransfer = DMax("[TransDate]", "Transfers", str_Criteria)
ret = DateDiff("d", date_LastTransfer, dd)


End If

getLastTransferDays = ret

End Function


Other information:
I am building a Tool Rental Data Base and need help with tracking length of time a tool is on a job and billing for it. I have my tables built and have Forms and Queries that Transfer a tool to a job.
The database is only tracking movement of a tool to a job and not from the job. I found this thread hoping it would help get me on track with a method to determine the date a tool leaves a job.

Someone may have a better solution to my project or some examples of similar data bases ?

Thanks

 

[plog]

Except for the values you changed in the code, you didn't post any table or field information for your database, so I don't have too much to go on. Could you post your data, or a sample and also include what you expected to be returned for that sample data?

Also in the meantime run this code and tell me if it returns all -1 values:

Public Function getLastTransferDays(ID, dd)
' gets days since last loan for a client id (id) and current disbursement date (dd)
ret = -1
' default value is negative to show no prior loan

str_Criteria = "[ToolNum]=" & ID & " AND [TransDate]<#" & dd & "#"
' criteria to use for DCount and DMax function calls
If DCount("[ToolNum]", "Tools", str_Criteria) > 0 Then
' if client had loans prior to current one, calculates days since last loan
date_LastTransfer = DMax("[TransDate]", "Transfers", str_Criteria)
ret = DateDiff("d", date_LastTransfer, dd)


End If

getLastTransferDays = ret

End Function

 

[dsthomson]

I will try this again. On my last attachment I exported only the Object that I thought were needed and pulled out my personal information. I did not enter enough information to show what was happening.

I have added multiple transfer dates for the same tool and would expect to find the day difference in those dates when I run the DaysBetweenTransfers query.
I ran the code provided and got all -1's.

Thanks for the help!

 

[plog]

The negative ones proved that this line of code never found any records:

If DCount("[ToolNum]", "Tools", str_Criteria) > 0 Then


Looking into it, that's because Tools didn't contain the field Transdate. To get my code to work you need one data source with both Tools and Transdate in it, your database doesn't have that datasource. So I created it with this SQL:

SELECT Tools.ToolNum, Transfers.TransDate
FROM Transfers INNER JOIN (Tools INNER JOIN ToolTransfer_Sub ON Tools.ToolNum = ToolTransfer_Sub.ToolNum) ON Transfers.TransID = ToolTransfer_Sub.TransID;

Paste that into a query and name that query 'subTransferDifferences'. Next you need to modify the code you have to find that new query. This is the correct code:

Public Function getLastTransferDays(ID, dd)
' gets days since last loan for a client id (id) and current disbursement date (dd)

ret = -1
' default value is negative to show no prior loan
 
str_Criteria = "[ToolNum]=" & ID & " AND [TransDate]<#" & dd & "#"
' criteria to use for DCount and DMax function callssubTransferDifferences

If DCount("[ToolNum]", "subTransferDifferences", str_Criteria) > 0 Then
' if client had loans prior to current one, calculates days since last loan

date_LastTransfer = DMax("[TransDate]", "subTransferDifferences", str_Criteria)

ret = DateDiff("d", date_LastTransfer, dd)

End If
 
getLastTransferDays = ret
 
End Function

Then finally, this SQL will use that function and return what you want:

SELECT Tools.ToolNum, Transfers.TransDate, getLastTransferDays([Tools]![ToolNum],[Transfers]![TransDate]) AS DaysSinceLastTransfer
FROM Transfers INNER JOIN (Tools INNER JOIN ToolTransfer_Sub ON Tools.ToolNum = ToolTransfer_Sub.ToolNum) ON Transfers.TransID = ToolTransfer_Sub.TransID
ORDER BY Tools.ToolNum, Transfers.TransDate;