Infamous Business Day Count

T

TechTutors

Hey Folks,

I've done my best to research this so that I wouldn't have to repost this
question ((probably the most commonly asked)). So, before I pose it I'd like
to thank you all for taking the time to help me out...

On the file I import is a "TransDate" On the table these records import
to, the "TransDate" field is set to date/time. On the same table, I have
created a "DaysAged" field that I hope will be populated by the result of
this "crude" formula:

"TransDate" - Today's Date - Weekends and Holidays

To elaborate... I need the number of business days that have transpired
between the original "TransDate" Weekends and holidays should be exluded
(obviously).

I have reviewed the code posted by Klatuu, and others... Unfortunately, I am
but a sprinkle-coder ((so to speak)) and the discussions often went over my
head. I'm hoping someone would be kind enough to show me some guidance in
the matter. More importantly, when I used Klatuu's code the following line
was in red and produced an error:

CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" & dtmStart & "# And #" & dtmEnd & "#")



The following is the code I grabbed from Klatuu in a few other posts:



'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" & dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function



Finally... I have already created tbl_Holidays w/ fields called Holdate
(date/time) and Holdate_Description (text)

Again, any and all help would greatly be appreciated.

Many Thanks!
-E
 
G

George Nicholson

If your table is called tbl_Holidays then:
DCount("*", "tbl_holidays", "[holdate] between #" & dtmStart & "# And #" &
dtmEnd & "#")

HTH,
 
T

TechTutors

Thanks Mr. Nicholson...

I had noticed that line prior to my post and made the changes just as you
suggested. Unfortunately, I still recieve the same error:

Compile error:
Expected: list separator or )

And those lines of code still remain red.

George Nicholson said:
If your table is called tbl_Holidays then:
DCount("*", "tbl_holidays", "[holdate] between #" & dtmStart & "# And #" &
dtmEnd & "#")

HTH,

--

TechTutors said:
Hey Folks,

I've done my best to research this so that I wouldn't have to repost this
question ((probably the most commonly asked)). So, before I pose it I'd
like
to thank you all for taking the time to help me out...

On the file I import is a "TransDate" On the table these records
import
to, the "TransDate" field is set to date/time. On the same table, I have
created a "DaysAged" field that I hope will be populated by the result of
this "crude" formula:

"TransDate" - Today's Date - Weekends and Holidays

To elaborate... I need the number of business days that have transpired
between the original "TransDate" Weekends and holidays should be exluded
(obviously).

I have reviewed the code posted by Klatuu, and others... Unfortunately, I
am
but a sprinkle-coder ((so to speak)) and the discussions often went over
my
head. I'm hoping someone would be kind enough to show me some guidance in
the matter. More importantly, when I used Klatuu's code the following
line
was in red and produced an error:

CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" & dtmStart & "# And #" & dtmEnd & "#")



The following is the code I grabbed from Klatuu in a few other posts:



'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding
Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate]
between
#" & dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function



Finally... I have already created tbl_Holidays w/ fields called Holdate
(date/time) and Holdate_Description (text)

Again, any and all help would greatly be appreciated.

Many Thanks!
-E
 
D

Dirk Goldgar

In
TechTutors said:
Thanks Mr. Nicholson...

I had noticed that line prior to my post and made the changes just as
you suggested. Unfortunately, I still recieve the same error:

Compile error:
Expected: list separator or )

And those lines of code still remain red.

George Nicholson said:
If your table is called tbl_Holidays then:
DCount("*", "tbl_holidays", "[holdate] between #" & dtmStart & "#
And #" & dtmEnd & "#")

Just in case: are you entering these code statements all on one line?
As they come through the newsreader, what were originally single lines
of code are being broken into two lines. To write a statement over
multiple lines requires the use of a continuation character.
 
T

TechTutors

You were right... The line had been split to two...

Now, here's the thing... I don't know where this module is supposed to go or
how exactly it works... Any sort of guidance or reference would be very much
appreciated.

Thanks!

Dirk Goldgar said:
In
TechTutors said:
Thanks Mr. Nicholson...

I had noticed that line prior to my post and made the changes just as
you suggested. Unfortunately, I still recieve the same error:

Compile error:
Expected: list separator or )

And those lines of code still remain red.

George Nicholson said:
If your table is called tbl_Holidays then:
DCount("*", "tbl_holidays", "[holdate] between #" & dtmStart & "#
And #" & dtmEnd & "#")

Just in case: are you entering these code statements all on one line?
As they come through the newsreader, what were originally single lines
of code are being broken into two lines. To write a statement over
multiple lines requires the use of a continuation character.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
T

TechTutors

I guess, my main question is how does this code know where to look for fields
like "dtmStart"? What I would like is for this module to run as one of the
steps in a list of processes.... To my understanding of this type of code, it
can't be triggered like a query or macro.... How can I have this automated
and act like one?

TechTutors said:
You were right... The line had been split to two...

Now, here's the thing... I don't know where this module is supposed to go or
how exactly it works... Any sort of guidance or reference would be very much
appreciated.

Thanks!

Dirk Goldgar said:
In
TechTutors said:
Thanks Mr. Nicholson...

I had noticed that line prior to my post and made the changes just as
you suggested. Unfortunately, I still recieve the same error:

Compile error:
Expected: list separator or )

And those lines of code still remain red.

:

If your table is called tbl_Holidays then:
DCount("*", "tbl_holidays", "[holdate] between #" & dtmStart & "#
And #" & dtmEnd & "#")

Just in case: are you entering these code statements all on one line?
As they come through the newsreader, what were originally single lines
of code are being broken into two lines. To write a statement over
multiple lines requires the use of a continuation character.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
G

George Nicholson

I don't know where this module is supposed to go

The function is best placed in a General code module (not one attached to a
Form, Report or other class object). In the VBE: View>Insert>Module. Make
sure the module is *not* given the same name as any of the functions you
might put there. "modGeneralCode" is ok to start.
I guess, my main question is how does this code know where to look for fields
like "dtmStart"?

What you have is a function that returns the number or workdays between 2
dates that *you supply it* as arguments.

From anywhere in your code you can call the function like any other:

iDays = CalcWorkDays(#1/1/2007#, #9/30/2007#)

you gave dtmStart the value of #1/1/2007#.
you gave dtmEnd the value of #9/30/2007#.
The function will do its calculation and return the results to iDays.
What I would like is for this module to run as one of the
steps in a list of processes.... To my understanding of this type of code,
it
can't be triggered like a query or macro.... How can I have this automated
and act like one?

Sorry, what process? What exactly do you want to do? Display on a form or
report? You can call the function from a query to show the result for each
record, but more specifics would help.

HTH,





TechTutors said:
I guess, my main question is how does this code know where to look for
fields
like "dtmStart"? What I would like is for this module to run as one of the
steps in a list of processes.... To my understanding of this type of code,
it
can't be triggered like a query or macro.... How can I have this automated
and act like one?

TechTutors said:
You were right... The line had been split to two...

Now, here's the thing... I don't know where this module is supposed to go
or
how exactly it works... Any sort of guidance or reference would be very
much
appreciated.

Thanks!

Dirk Goldgar said:
In Thanks Mr. Nicholson...

I had noticed that line prior to my post and made the changes just as
you suggested. Unfortunately, I still recieve the same error:

Compile error:
Expected: list separator or )

And those lines of code still remain red.

:

If your table is called tbl_Holidays then:
DCount("*", "tbl_holidays", "[holdate] between #" & dtmStart & "#
And #" & dtmEnd & "#")

Just in case: are you entering these code statements all on one line?
As they come through the newsreader, what were originally single lines
of code are being broken into two lines. To write a statement over
multiple lines requires the use of a continuation character.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
J

jokenjo

George Nicholson a écrit :
I don't know where this module is supposed to go

The function is best placed in a General code module (not one attached to a
Form, Report or other class object). In the VBE: View>Insert>Module. Make
sure the module is *not* given the same name as any of the functions you
might put there. "modGeneralCode" is ok to start.
I guess, my main question is how does this code know where to look for fields
like "dtmStart"?

What you have is a function that returns the number or workdays between 2
dates that *you supply it* as arguments.

From anywhere in your code you can call the function like any other:

iDays = CalcWorkDays(#1/1/2007#, #9/30/2007#)

you gave dtmStart the value of #1/1/2007#.
you gave dtmEnd the value of #9/30/2007#.
The function will do its calculation and return the results to iDays.
What I would like is for this module to run as one of the
steps in a list of processes.... To my understanding of this type of code,
it
can't be triggered like a query or macro.... How can I have this automated
and act like one?

Sorry, what process? What exactly do you want to do? Display on a form or
report? You can call the function from a query to show the result for each
record, but more specifics would help.

HTH,





TechTutors said:
I guess, my main question is how does this code know where to look for
fields
like "dtmStart"? What I would like is for this module to run as one of the
steps in a list of processes.... To my understanding of this type of code,
it
can't be triggered like a query or macro.... How can I have this automated
and act like one?

TechTutors said:
You were right... The line had been split to two...

Now, here's the thing... I don't know where this module is supposed to go
or
how exactly it works... Any sort of guidance or reference would be very
much
appreciated.

Thanks!

:

In Thanks Mr. Nicholson...

I had noticed that line prior to my post and made the changes just as
you suggested. Unfortunately, I still recieve the same error:

Compile error:
Expected: list separator or )

And those lines of code still remain red.

:

If your table is called tbl_Holidays then:
DCount("*", "tbl_holidays", "[holdate] between #" & dtmStart & "#
And #" & dtmEnd & "#")
Just in case: are you entering these code statements all on one line?
As they come through the newsreader, what were originally single lines
of code are being broken into two lines. To write a statement over
multiple lines requires the use of a continuation character.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with my function vba code 1
CalcWorkDays error 2
Can't query DateDiff 0
Number of days 9
Calculating Date/Time Fields 1
Format Days To Hours 0
can someone explain this 1
Calculate Buisness Days Open 2

Top