Full Version: Using Datediff to calculate days between dates and now
UtterAccess Forums > Microsoft® Access > Access Date + Time
I need to generate a report which calculates the days between entered dates but also to calculate between one date and now if the date in field is emplty. Let me explain:
I have a tool which is rented out for x amount of days:
Date Out Date In Days Rented
1/1/2005 1/2/2005 31
That parts easy using Datediff
But if the Date In field is null (empty) then I have to calculate the number of days from the Date Out till now (the day the report is generated).
I think the psuedo code goes like this:
If Date In has a value
Then calculate days between Date Out and Date In
If Date In does not have a value
Then Calculate days between Date Out and today.
Any help on this would be much appreciated.
Check Access / Access VB Help on the following function:
and you already knew the DateDiff().
Larry Larsen
Here's a small function to run your dates through..
Function BetweenDates(inDate1 As Variant, Optional inDate2 As Variant) As Long
Dim dDiff As Integer
[color="green"]'/ Let's check to see if the second dates "Missing"[/color]
If IsMissing(inDate2) Then
[color="green"]'/ If it is assign it todays date..[/color]
inDate2 = Date
End If
[color="green"]'/ Let's use the DateDiff() function to calculate the diff..[/color]
dDate = DateDiff("d", inDate1, inDate2)
[color="green"]'/ Result..[/color]
BetweenDates = dDate
And Function

intTotalDays = DateDiff("d", Nz(dtmDateIn, Date()), dtmDateOut)
I think the O.P. implied DateOut is always earlier than (or at least same as) DateIn like library book loans???
On that case, the expression should probably be:
intTotalDays = DateDiff("d", dtmDateOut, Nz(dtmDateIn, Date()))
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.