Re: Using a module in a query
From: Ted Allen (anonymous_at_discussions.microsoft.com)
Date: 06/02/04
- Next message: Marc: "Re: Odd error message"
- Previous message: Linda: "3075 error"
- In reply to: Fatz: "Re: Using a module in a query"
- Next in thread: Rick Brandt: "Re: Using a module in a query"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 2 Jun 2004 07:17:55 -0700
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" <anonymous@discussions.microsoft.com> wrote
in message news:<16b3101c44827$a14e6390
$a301280a@phx.gbl>...
>> 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)
>> >.
>> >
>.
>
- Next message: Marc: "Re: Odd error message"
- Previous message: Linda: "3075 error"
- In reply to: Fatz: "Re: Using a module in a query"
- Next in thread: Rick Brandt: "Re: Using a module in a query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
