Using a module in a query

I am trying to call the following module (which I borrowed from "The
Access Web") to calculate the number of business days. Here is the
module:

Function Work_Days(BegDate, EndDate) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt EndDate
If Format(DateCnt, "ddd") "Sun" And _
Format(DateCnt, "ddd") "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
'*********** Code End **************

I have a query that has BegDate and EndDate and I want to add a new
column that will call this module and give me the number of business
days. The problem is this...I have never called a module before!!
Can someone please provide me with a detailed explanation of exactly
what needs to be written to get this to work???

Thanks so much!

Chris (Newbie)

Hi Chris,

Assuming that you have already pasted the function into a
module and saved it (if not you must do this first), all
you have to do to use the function is reference it like
any built in function.

So, if you have a query with fields titled StartDate and
EndDate (the date fields could have any name), you just
enter the following expression in a blank column in your
query builder:

Work_Days([StartDate],[EndDate])

Note that the []'s are really only needed if the field
names have spaces.

If you want to give the column a meaningful name, you can
enter it before the expression followed by a colon, such
as:

Working Days: Work_Days([StartDate],[EndDate])

One final note, any custom functions that have been saved
in modules in your database are visible using the
builder. To see this, go to a field in a blank column
and right-click then choose "Build...". When the builder
opens, choose functions and then you will see a listing
for built in functions and also a listing with your
database name. If you double-click your database name
you will see your modules, and if you click on any of
them you will see the custom functions they contain.
Double-Clicking a custom function will insert it into
your field expression, with the variable names as
placeholders. But, you then substitute your field names
for the variable names.

HTH, Ted Allen
-----Original Message-----
I am trying to call the following module (which I
borrowed from "The
Access Web") to calculate the number of business days.
Here is the
module:

Function Work_Days(BegDate, EndDate) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt EndDate
If Format(DateCnt, "ddd") "Sun" And _
Format(DateCnt, "ddd")
"Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
'*********** Code End **************

I have a query that has BegDate and EndDate and I want
to add a new
column that will call this module and give me the number
of business
days. The problem is this...I have never called a
module before!!
Can someone please provide me with a detailed
explanation of exactly
what needs to be written to get this to work???

Thanks so much!

Chris (Newbie)
.

"Fatz" wrote in message
om...
I am trying to call the following module (which I borrowed from "The
Access Web") to calculate the number of business days. Here is the
module:

Function Work_Days(BegDate, EndDate) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt EndDate
If Format(DateCnt, "ddd") "Sun" And _
Format(DateCnt, "ddd") "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
'*********** Code End **************

I have a query that has BegDate and EndDate and I want to add a new
column that will call this module and give me the number of business
days. The problem is this...I have never called a module before!!
Can someone please provide me with a detailed explanation of exactly
what needs to be written to get this to work???

You don't call modules as they are just containers for functions and
sub-routines. If you look at your module again you will see that it actually
contains a custom function named Work_Days(). You use custom functions in a
query exactly the same as you would use a built-in function.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Ted-

Thanks so much. I was using the name that I saved the module as
instead of the name of the function! Typed in what you said and it
worked perfectly. I appreciate the help and the detailed explanation!

Cheers!
Chris


"Ted Allen" wrote in message ...
Hi Chris,

Assuming that you have already pasted the function into a
module and saved it (if not you must do this first), all
you have to do to use the function is reference it like
any built in function.

So, if you have a query with fields titled StartDate and
EndDate (the date fields could have any name), you just
enter the following expression in a blank column in your
query builder:

Work_Days([StartDate],[EndDate])

Note that the []'s are really only needed if the field
names have spaces.

If you want to give the column a meaningful name, you can
enter it before the expression followed by a colon, such
as:

Working Days: Work_Days([StartDate],[EndDate])

One final note, any custom functions that have been saved
in modules in your database are visible using the
builder. To see this, go to a field in a blank column
and right-click then choose "Build...". When the builder
opens, choose functions and then you will see a listing
for built in functions and also a listing with your
database name. If you double-click your database name
you will see your modules, and if you click on any of
them you will see the custom functions they contain.
Double-Clicking a custom function will insert it into
your field expression, with the variable names as
placeholders. But, you then substitute your field names
for the variable names.

HTH, Ted Allen
-----Original Message-----
I am trying to call the following module (which I
borrowed from "The
Access Web") to calculate the number of business days.
Here is the
module:

Function Work_Days(BegDate, EndDate) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt EndDate
If Format(DateCnt, "ddd") "Sun" And _
Format(DateCnt, "ddd")
"Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
'*********** Code End **************

I have a query that has BegDate and EndDate and I want
to add a new
column that will call this module and give me the number
of business
days. The problem is this...I have never called a
module before!!
Can someone please provide me with a detailed
explanation of exactly
what needs to be written to get this to work???

Thanks so much!

Chris (Newbie)
.

My pleasure, glad it helped.

-----Original Message-----
Ted-

Thanks so much. I was using the name that I saved the
module as
instead of the name of the function! Typed in what you
said and it
worked perfectly. I appreciate the help and the
detailed explanation!

Cheers!
Chris


"Ted Allen" wrote
in message news:16b3101c44827$a14e6390
...
Hi Chris,

Assuming that you have already pasted the function
into a
module and saved it (if not you must do this first),
all
you have to do to use the function is reference it
like
any built in function.

So, if you have a query with fields titled StartDate
and
EndDate (the date fields could have any name), you
just
enter the following expression in a blank column in
your
query builder:

Work_Days([StartDate],[EndDate])

Note that the []'s are really only needed if the field
names have spaces.

If you want to give the column a meaningful name, you
can
enter it before the expression followed by a colon,
such
as:

Working Days: Work_Days([StartDate],[EndDate])

One final note, any custom functions that have been
saved
in modules in your database are visible using the
builder. To see this, go to a field in a blank column
and right-click then choose "Build...". When the
builder
opens, choose functions and then you will see a
listing
for built in functions and also a listing with your
database name. If you double-click your database name
you will see your modules, and if you click on any of
them you will see the custom functions they contain.
Double-Clicking a custom function will insert it into
your field expression, with the variable names as
placeholders. But, you then substitute your field
names
for the variable names.

HTH, Ted Allen
-----Original Message-----
I am trying to call the following module (which I
borrowed from "The
Access Web") to calculate the number of business
days.
Here is the
module:

Function Work_Days(BegDate, EndDate) As Integer
' Note that this function does not account for
holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt EndDate
If Format(DateCnt, "ddd") "Sun" And _
Format(DateCnt, "ddd")
"Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
'*********** Code End **************

I have a query that has BegDate and EndDate and I
want
to add a new
column that will call this module and give me the
number
of business
days. The problem is this...I have never called a
module before!!
Can someone please provide me with a detailed
explanation of exactly
what needs to be written to get this to work???

Thanks so much!

Chris (Newbie)
.

.