Substracting dates

[Andy]

Hi Everyone,

I have this database and there are two fields, Start Date and End Date. I
would like somehow to calculate how many working days are between End Date
and Start Date and save the value into another field.

Can anyone help me with this please.

Regards,

Anguel

 

[Douglas J Steele]

You should never store calculated values in a table.

As fellow MVP John VInson likes to say "Storing calculated data generally
accomplishes only three things: it wastes disk space, it wastes time (a disk
fetch is much slower than almost any reasonable calculation), and it risks
data validity, since once it's stored in a table either the Total or one of
the fields that goes into the total may be changed, making the value WRONG."

Instead, you should create a query that includes the calculation, and use
the query rather than the table.

For a couple of options for how to calculate the difference, see the
Date/Time section of "The Access Web" http://www.mvps.org/access/ You could
also check out my September, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Guest]

Here is a function that calculates the number of working days between two
dates. It includes looking up dates in a Holiday table to determine if a
week day is a holiday. If you have a holiday table in your app, you will
need to change the name [Holdate] to whatever your's is. If you don't have a
holiday table, I would suggest one. Mine has only two fields, Holdate
(date/time) that is a non working day and a description field to identifiy
the holiday. If you don't want to omit holidays, you can remove the code
that checks for holidays.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'Calculates the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

 

[Guest]

Andy,
Douglas is correct, I forgot to mention that in my previous post; however,
the function I posted can still be used to calculate and display the results
whenever it is needed.

 

[Andy]

Hi Klatuu,

Where do I enter the script? just in the form?

Andy

Here is a function that calculates the number of working days between two
dates. It includes looking up dates in a Holiday table to determine if a
week day is a holiday. If you have a holiday table in your app, you will
need to change the name [Holdate] to whatever your's is. If you don't
have a
holiday table, I would suggest one. Mine has only two fields, Holdate
(date/time) that is a non working day and a description field to identifiy
the holiday. If you don't want to omit holidays, you can remove the code
that checks for holidays.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'Calculates the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total
days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare
date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

Hi Everyone,

I have this database and there are two fields, Start Date and End Date. I
would like somehow to calculate how many working days are between End
Date
and Start Date and save the value into another field.

Can anyone help me with this please.

Regards,

Anguel

 

[Guest]

You can, but I would put it in a standard module. That way, it will be
available to other forms, reports, or queries in your application.

Hi Klatuu,

Where do I enter the script? just in the form?

Andy

Here is a function that calculates the number of working days between two
dates. It includes looking up dates in a Holiday table to determine if a
week day is a holiday. If you have a holiday table in your app, you will
need to change the name [Holdate] to whatever your's is. If you don't
have a
holiday table, I would suggest one. Mine has only two fields, Holdate
(date/time) that is a non working day and a description field to identifiy
the holiday. If you don't want to omit holidays, you can remove the code
that checks for holidays.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'Calculates the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total
days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare
date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

Hi Everyone,

I have this database and there are two fields, Start Date and End Date. I
would like somehow to calculate how many working days are between End
Date
and Start Date and save the value into another field.

Can anyone help me with this please.

Regards,

Anguel

 

[Andy]

Thanks,

I tired it but it is not working. I guess I am too stupid to work it out
:-(.

I have put your code in the global module so it is available for forms and
reports. I have also modified the code to match my fields please see below
(am I right in doing so?). How do I tell the database that the field Time =
the output of the function you gave me?

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'Calculates the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total
days
'Add one to include
First Day
dtmToday = dtmStartDate 'Initiate compare
date
Do Until dtmToday > dtmEndDate
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day

ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEndDate All days have
been compared
CalcWorkDays = intTotalDaysime 'Return the
value
End Function

 

[Douglas J Steele]

Your last line should be

CalcWorkDays = intTotalDays

not

CalcWorkDays = intTotalDaysime


The fact that you're not getting an error implies that you haven't told
Access to Require Variable Declaration (on the Module tab under Tools |
Options when you're in the VB Editor). If you had that turned on, Access
would have caught the fact that you hadn't declared the variable
intTotalDaysime and raised an error.

When you have that option on, the first two lines of the module should be

Option Compare Database
Option Explicit

 

[Andy]

I have made the change. Still no result and no error message either.

Anguel

 

[Guest]

As Douglas said, you are trying to return a value for a variable that does
not exist in the function. I don't see any field name changes. The only
changes you would want to make would be here:

ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday

Where [HolDate] is the name of the date field in the table Holidays.

My guess is you are trying to get your varialble intTotalDaysime to receive
the value of the function. That's not how it works. If you are trying to
get the results into this variable, then it should be:

intTotalDaysime = CalcWorkDays(dtmStartDate, dtmEndDate)