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

anonymous_at_discussions.microsoft.com
Date: 08/26/04 

Date: Wed, 25 Aug 2004 21:29:34 -0700

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.

>-----Original Message-----
>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!)
>
>
>"Michael" <mness23@hotmail.com> wrote in message
>news:bfcd01c489a9$8b61fe70$a401280a@phx.gbl...
>> 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.
>>
>>
>
>
>.
>

Relevant Pages

  • Re: changed queries save automatically
    ... MS is very aware of this bug, and it's been looked at in many ... Just today I create a new query in A03 ... macro or code someone has Set Warnings to Off but forgot to Set ... Compacting and repairing closes the database and reopens it. ...
    (microsoft.public.access.queries)
  • Re: changed queries save automatically
    ... Check out Allen Browne's "bug" about Name AutoCorrect. ... A query not prompting you about saving it is one of the symptoms of this ... Either something else is turning off warnings or we're dealing with a bug ... Compacting and repairing closes the database and reopens it. ...
    (microsoft.public.access.queries)
  • Re: bug in access calling function from insert into query???
    ... the parameter passed to it never changes during the processing of the query. ... When you use a parameter query, this adds steps to the optimization process, ... a SQL statement is used to give the database engine commands to ... This behavior is by design, so you won't be able to fix it. ...
    (microsoft.public.access.queries)
  • Re: IS NULL not working in WHERE clause.
    ... At this point it's pretty hard to say whether it's a bug or corruption. ... And just to be certain - you said the query operators each select ... in the query plan that checks time_id for null, ... >This execution plan shows a single execution string. ...
    (microsoft.public.sqlserver.programming)
  • Re: union all
    ... It certainly seems like a bug to me if 8 million rows vanish when you ... Provide the CREATE TABLE statements for CLAIMFL, CHKREG, CLIENT, ... script below (with your query inserted) in Query Analyzer and posting ... >union all ...
    (microsoft.public.sqlserver.programming)