Using a module in a query

F

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

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
 
R

Rick Brandt

Fatz said:
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???
Click to expand...

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

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


Ted Allen said:
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")
Click to expand...
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)
.
Click to expand...
Click to expand...
 
T

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
Click to expand...
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
Click to expand...
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")
Click to expand...
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
Click to expand...
want
to add a new
column that will call this module and give me the
Click to expand...
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)
.
Click to expand...
Click to expand...
.
Click to expand...
 

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

Calculate # of Business Days 1
Working Days 4
adding work days to a begin date to derive the end date 1
3rd Business Day 4
business days 6
Passing Fields to Module for Calculation in Query 2
Calculating working days between dates 1
Date VBA Code 1

Top