Using a module in a query

[Fatz]

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)

 

[Ted Allen]

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

 

[Rick Brandt]

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.

 

[Fatz]

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

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")

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

 

[Ted Allen]

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" <[email protected]> wrote

in message [email protected]>...

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")

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

.