Infamous Business Day Count

[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

 

[George Nicholson]

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

HTH,

 

[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.

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

HTH,

--

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

 

[Dirk Goldgar]

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.

 

[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!

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)

 

[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?

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)

 

[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,

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?

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)

 

[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,

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?

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)