Total Number of Days

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a travel database I have "StartDate" which is the beginning date of travel
and "EndDate" which is the ending date of travel. I need to create a field
"TotalDays" that will calculate how many business (ie non weekend days)
between the StartDate and EndDate. I know how to do this in Excel, but don't
have a clue how to achieve this in a database.

Thanks in advance!
 
Look up the datediff function in the help screen. You can use this in the
control source of a text box or in a query.
 
Thanks! That worked great! In about 5-6 months I should have this thing
finally up and running!
 
schasteen said:
Look up the datediff function in the help screen. You can use this in the
control source of a text box or in a query.

:
Click to expand...

Another way is to use DateDiff to get the total number of days, then use:

Public Function CountWeekendDays(dtStart As Date, dtEnd As Date)
Dim intSat As Integer
Dim intSun As Integer

'This function assumes dtStart <= dtEnd
CountWeekendDays = 0
intSat = (LEDay(dtEnd, 7) - GEDay(dtStart, 7)) / 7 + 1
intSun = (LEDay(dtEnd, 1) - GEDay(dtStart, 1)) / 7 + 1
CountWeekendDays = (intSat + intSun + Abs(intSat) + Abs(intSun)) / 2
End Function

Private Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

Private Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function


to get the number of weekend days to subtract from the total.

James A. Fortune
(e-mail address removed)
 
Thanks. I read the "Working All Day" report. Kudos on the clever titles for
all of your "Smart Access" articles. I think my original question wasn't
worded very well - I noticed several other posts that have a similar issue -
but I don't understand VB very well so a lot of the answers don't really help
me get to a solution.

I need to have my dates be inclusive of the dates that are listed. So if my
StartDate is "02/01/2006" and my EndDate is "02/07/2006" I need it to return
a value of 5 workdays.

I'm very new to access - I've had some success with Macros in Excel, but I
haven't tried any in Access, so I'm not even really sure where to begin.

From the article it appears that I need to use the the following code:
Function WorkDayDiff( _
DateFrom As Date , _
DateTo As Date _
) As Long
WorkDayDiff = DateDiff("d", DateFrom, DateTo) - _
DateDiff("ww", DateFrom, DateTo, 1) * 2 - _
IIf(Weekday(DateTo, 1) = 7, _
IIf(Weekday(DateFrom, 1) = 7, 0, 1), _
IIf(Weekday(DateFrom, 1) = 7, -1, 0))

Substituting StartDate and EndDate for DateFrom and DateTo since those are
the names that I used for my fields.

My quesiton is, where would I put this. The "totaldays" field is something
I'm using in a Query and a Report. But I don't have a clue where I would
actually set up this function. Is it in the Query Builder in the same way I
was using the DateDiff function?

Thanks for your help!
 
There was no need to substitute the names: parameters in functions are just
"place holders" for the values that you pass to them. Also, it's not clear
whether you just forgot to include the End Function in what you pasted: you
definitely need that line as well.

Create a new module, and store that function in the new module. Do not name
the module WorkDayDiff: modules cannot have the same name as functions or
subs.

Now, create a query that includes your DateFrom and DateTo fields. Add a
computed field to that query by typing the following into an empty cell on
the "Field" line in the query builder:

TotalDays: WorkDayDiff([DateFrom], [DateTo])

If there's a chance that DateFrom or DateTo might be null, you'll need to
make some changes. What do you want to do in that case: perhaps use today's
date instead of the Null? If so, change the computed field from above to:

TotalDays: WorkDayDiff(Nz([DateFrom], Date()), Nz([DateTo], Date()))

Otherwise, you'll have to change the function declaration from

Function WorkDayDiff( _
DateFrom As Date , _
DateTo As Date _
) As Long

to

Function WorkDayDiff( _
DateFrom As Variant , _
DateTo As Variant _
) As Long

and add checking in the function to correctly handle Null values. The
revised function below will return 0 if either of the dates are Null:

Function WorkDayDiff( _
DateFrom As Variant , _
DateTo As Variant _
) As Long

If IsNull(DateFrom) Or IsNull(DateTo) Then
WorkDayDiff = 0
Else
WorkDayDiff = DateDiff("d", DateFrom, DateTo) - _
DateDiff("ww", DateFrom, DateTo, 1) * 2 - _
IIf(Weekday(DateTo, 1) = 7, _
IIf(Weekday(DateFrom, 1) = 7, 0, 1), _
IIf(Weekday(DateFrom, 1) = 7, -1, 0))
End If

End Function
 
Back
Top