bug in access calling function from insert into query???

[Michael]

Hi All,

I posted here yesterday regarding this problem thinking it
was related to

Microsoft Knowledge Base Article - 304098
ACC2000: Custom Function Called by a Query Runs Multiple
Times on a Computer Running Windows 2000 SP1

but now I'm not so sure as the problem above relates to
calling a custom fuction from the criteria of a query and
Ive installed sp8 as it suggests.

Can someone please tell me if I'm doing something wrong.
If I call the following query it calls test once as it
should.

SELECT test() AS Expr1
FROM KeyTable;

However as soon as I stick a parameter in like below which
I need to do in my real query.

PARAMETERS test Short;
SELECT test() AS Expr1
FROM KeyTable;

It runs the function test 3 times.
Is this a bug in access and can it be fixed or have I done
something wrong?

test is defined as

Function Test()
Debug.Print "Testing the Function."
End Function

Im running Windows 2000 (sp3) and access 2000 (jet 4.0 sp8)
Thanks.

 

['69 Camaro]

Hi, Michael.

have I done something wrong?

Sort of. You are making assumptions on how it should work. Therefore,
you're not using it in quite the way that it was designed, so you are
getting unexpected results.

Jet optimizes queries so that a single function will only be called once if
the parameter passed to it never changes during the processing of the query.
That's why you saw the print statement in the Immediate window only once
when you ran the original query, no matter how many records were in your
table.

When you use a parameter query, this adds steps to the optimization process,
so the function gets called more than once, because Jet is testing it while
optimizing it. Apparently, there are two passes for the parameter during
optimization.

Generally, a SQL statement is used to give the database engine commands to
carry out on the data contained in the database and return the appropriate
data set. Your test( ) function is not passed any data from any rows
because no rows are retrieved from the database. If you change this in the
future by adding a field name as the value passed to the test( ) function
(you'd also have to add a parameter to the function definition, of course),
and that value varies from record to record, then the test( ) function will
be called once for every record in your table, plus the two passes for the
parameter optimization.

This behavior is by design, so you won't be able to fix it. If your
user-defined function can only be called once for the entire table (or once
for each record if you pass a varying field value to the function), such as
when your function is incrementing a value each time it is called, then this
is not the way to accomplish your task.

You gave a very generic example of your SQL statement. Perhaps if you gave
us some more details of what you'd like to accomplish, then we could offer
some suggestions for alternatives.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Any human can read my reply E-mail address and should alter it so that a
message will be forwarded to me. Spammers are free to use my UNALTERED
reply E-mail address. I will *never* get those messages!)

 

[Guest]

Thanks for that,

I've got a solution but just wanted to check it wasn't a
bug with access that could be fixed with a service pack or
something.

 

['69 Camaro]

You're welcome.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Any human can read my reply E-mail address and should alter it so that a
message will be forwarded to me. Spammers are free to use my UNALTERED
reply E-mail address. I will *never* get those messages!)