VB Functions in Access Module

[Chris Hohmann]

"Sean S" <sespark@webprod.net> wrote in message
news:00bf01c37257$4b9c1fb0$3501280a@phx.gbl...

> I am using a Access query to select a list of information
> from a table. I use function defined in a vb module in
> the Access database. The function is called in the query
> which is written in the database. The query is called
> qryAction and is as follows:
> PARAMETERS ID Short, Appointment1 Short, DueDateI
> DateTime, Employee Short;
> SELECT Action.*, DerivedDate
> (Action.Month,Action.Day,Action.Minute,Appointment .Meeting
> Time,Action.Prior) AS DueDate
> FROM ((Client RIGHT JOIN Matter ON Client.ClientID =
> Matter.ClientID) RIGHT JOIN Retainer ON Matter.MatterID =
> Retainer.MatterID) RIGHT JOIN (Appointment RIGHT JOIN
> [Action] ON Appointment.AppointmentID =
> Action.AppointmentID) ON Retainer.RetainerID =
> Action.RetainerID
> WHERE blah;
>
> DerivedDate is said function and is as follows:
> Function DerivedDate(ByVal numMonth As Integer, ByVal
> numDay As Integer, ByVal numMinute As Integer, ByVal
> dateOriginal As Date, ByVal boolPrior As Boolean) As Date
> Dim dateNew, floatPrior
> If boolPrior Then
> floatPrior = -1
> Else
> floatPrior = 1
> End If
> dateNew = dateOriginal
> numMonth = numMonth * floatPrior
> numDay = numDay * floatPrior
> numMinute = numMinute * floatPrior
> dateNew = DateAdd("m", numMonth, dateNew)
> dateNew = DateAdd("y", numDay, dateNew)
> dateNew = DateAdd("n", numMinute, dateNew)
> DerivedDate = dateNew
> End Function

SELECT
Action.*,
DateAdd("m",IIF(Action.Prior,0-Action.Month,Action.Month),
DateAdd("y",IIF(Action.Prior,0-Action.Day,Action.Day),
DateAdd("n",IIF(Action.Prior,0-Action.Minute,Action.Minute),Appointment.
Meeting))) AS DueDate
FROM
..
..
..

Notes:
1. When possible, please avoid the use of "SELECT *". Here's an article
on why: [url]http://aspfaq.com/2096[/url]
2. Consider redesigning the Action table as the values for
Month,Day,Minute and Prior can and should be represented in a datetime
value.

HTH
-Chris

[Bob Barrows]

Due to security reasons, you cannot run queries that call custom VBA
functions from external programs: Access must be running in order for them
to run. We've been cursing about this for years, but the security argument
does tend to quell those grumbles.

In addition to custom VBA functions, several builtin VBA functions cannot be
called in jet queries as well.This page contains the list of VBA builtin
functions that can be used in Jet queries run from external applications
such as VB and ASP:
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;239482[/url]

You are going to have to come up with another way to implement the
functionality of the DerivedDate function.

HTH,
Bob Barrows

Sean S wrote:

> I am using a Access query to select a list of information
> from a table. I use function defined in a vb module in
> the Access database. The function is called in the query
> which is written in the database. The query is called
> qryAction and is as follows:

[Sean S]

Thank you very much for your quick reply. :D That's too
bad. :(

>-----Original Message-----
>Due to security reasons, you cannot run queries that

call custom VBA

>functions from external programs: Access must be running

in order for them

>to run. We've been cursing about this for years, but the

security argument

>does tend to quell those grumbles.
>
>In addition to custom VBA functions, several builtin VBA

functions cannot be

>called in jet queries as well.This page contains the

list of VBA builtin

>functions that can be used in Jet queries run from

external applications

>such as VB and ASP:
>[url]http://support.microsoft.com/default.aspx?scid=kb;en-[/url]

us;239482

>
>You are going to have to come up with another way to

implement the

>functionality of the DerivedDate function.
>
>HTH,
>Bob Barrows
>
>Sean S wrote:

>> I am using a Access query to select a list of

information

>> from a table. I use function defined in a vb module in
>> the Access database. The function is called in the

query

>> which is written in the database. The query is called
>> qryAction and is as follows:

>
>
>.
>