ACC2K: Function in query expression called multiple times 
Author Message
 ACC2K: Function in query expression called multiple times

I have a public function that is called from a query expression.  The
query takes a parameter, and passes that parameter on to the function.

When the query is executed the function gets called 3 times.
The sequence of events is:
1: Called before any input box appears for the parameter.
2: Query asks for parameter
3: Function called again with no parameter passed
4: Function called with parameter populated.

The "real" function and query are fairly complex, and involve updating
the database - it's my way of emulating a stored procedure to clone a
hierarchy of records.  However the problem can be simply reproduced as
follows:
1. Create a new database.
2. Create a module and add the following function:
Public Function TestFunc(ByVal ilNumber As Long) As Long

    If ilNumber = 0 Then
        MsgBox "Number is zero", , "Test Function"
    Else
        TestFunc = (2 * ilNumber)
    End If

End Function

3: Save the module
4: Create a query that calls the function as follows (SQL view):
PARAMETERS p_Number Long;
SELECT TestFunc([p_Number]) AS Expr1;

5: Run the query.

Note that in Access 97, the query processor will not allow a query
without a table, therefore a dummy table must be added.  Adding a table
and running this in Access 97 produces expected results - ie no message
box.  If a table in Access 2000 is added, the problem still occurs.

I can get around the problem by testing the function, but I'd still like
to understand whats going on.

Has anyone else hit a similar problem?

Rgds
Mark



Mon, 13 May 2002 03:00:00 GMT  
 ACC2K: Function in query expression called multiple times
After more thought, I tried the following query:

PARAMETERS p_Number Long;
SELECT TestFunc(Eval([p_Number])) AS Expr1;

This works!  Could there be a problem in the optimiser?

Cheers
Mark

Quote:
-----Original Message-----
I have a public function that is called from a query expression.  The
query takes a parameter, and passes that parameter on to the function.

When the query is executed the function gets called 3 times.
The sequence of events is:
1: Called before any input box appears for the parameter.
2: Query asks for parameter
3: Function called again with no parameter passed
4: Function called with parameter populated.

The "real" function and query are fairly complex, and involve updating
the database - it's my way of emulating a stored procedure to clone a
hierarchy of records.  However the problem can be simply reproduced as
follows:
1. Create a new database.
2. Create a module and add the following function:
Public Function TestFunc(ByVal ilNumber As Long) As Long

    If ilNumber = 0 Then
        MsgBox "Number is zero", , "Test Function"
    Else
        TestFunc = (2 * ilNumber)
    End If

End Function

3: Save the module
4: Create a query that calls the function as follows (SQL view):
PARAMETERS p_Number Long;
SELECT TestFunc([p_Number]) AS Expr1;

5: Run the query.

Note that in Access 97, the query processor will not allow a query
without a table, therefore a dummy table must be added.  Adding a table
and running this in Access 97 produces expected results - ie no message
box.  If a table in Access 2000 is added, the problem still occurs.

I can get around the problem by testing the function, but I'd still like
to understand whats going on.

Has anyone else hit a similar problem?

Rgds
Mark



Tue, 14 May 2002 03:00:00 GMT  
 ACC2K: Function in query expression called multiple times
Since the parameter is a constant for the duration of the query,
it will only be run once, that is expected. If thats your goal,
then this is a reasonable solution.

--
MichKa

don't ask by e-mail unless you're paying for it. (TANSTAAFL!) :-)

random junk of dubious value and the worldwide TSI Form/Report
to Data Access Page Wizard at http://www.trigeminal.com/


message

Quote:
> After more thought, I tried the following query:

> PARAMETERS p_Number Long;
> SELECT TestFunc(Eval([p_Number])) AS Expr1;

> This works!  Could there be a problem in the optimiser?

> Cheers
> Mark
> -----Original Message-----
> I have a public function that is called from a query
expression.  The
> query takes a parameter, and passes that parameter on to the
function.

> When the query is executed the function gets called 3 times.
> The sequence of events is:
> 1: Called before any input box appears for the parameter.
> 2: Query asks for parameter
> 3: Function called again with no parameter passed
> 4: Function called with parameter populated.

> The "real" function and query are fairly complex, and involve
updating
> the database - it's my way of emulating a stored procedure to
clone a
> hierarchy of records.  However the problem can be simply
reproduced as
> follows:
> 1. Create a new database.
> 2. Create a module and add the following function:
> Public Function TestFunc(ByVal ilNumber As Long) As Long

>     If ilNumber = 0 Then
>         MsgBox "Number is zero", , "Test Function"
>     Else
>         TestFunc = (2 * ilNumber)
>     End If

> End Function

> 3: Save the module
> 4: Create a query that calls the function as follows (SQL
view):
> PARAMETERS p_Number Long;
> SELECT TestFunc([p_Number]) AS Expr1;

> 5: Run the query.

> Note that in Access 97, the query processor will not allow a
query
> without a table, therefore a dummy table must be added.  Adding
a table
> and running this in Access 97 produces expected results - ie no
message
> box.  If a table in Access 2000 is added, the problem still
occurs.

> I can get around the problem by testing the function, but I'd
still like
> to understand whats going on.

> Has anyone else hit a similar problem?

> Rgds
> Mark



Tue, 14 May 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Documenting query expressions and function calls

2. Call an Access Query : Undefined Function in Expression

3. Error 3075: function isn't available in expressions in query expression

4. Function isn't available in expressions in query expression

5. Help: Function isn't available in expressions in query expression

6. multiple functions calling a common DAO function

7. Function isn't available in expressions in query

8. Undefined function 'nz' in query expression

9. ACC2K: Problem with query's Where condition and DAO

10. Calling multiple queries through VBA

11. Access has problem when deleting query multiple times?

12. changing multiple queries at run time

 

 
Powered by phpBB® Forum Software