Re: bug in access calling function from insert into query???
From: '69 Camaro (ForwardZERO_SPAM.To.69Camaro_at_Spameater.orgZERO_SPAM)
Date: 08/26/04
- Next message: Lamar: "Blank Row in Query"
- Previous message: Richard Hollenbeck: "Re: append (INSERT INTO) query problems"
- In reply to: anonymous_at_discussions.microsoft.com: "Re: bug in access calling function from insert into query???"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 26 Aug 2004 08:51:02 -0700
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!)
<anonymous@discussions.microsoft.com> wrote in message
news:058601c48b25$49f6d320$a401280a@phx.gbl...
> 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.
> >>
> >>
> >
> >
> >.
> >
- Next message: Lamar: "Blank Row in Query"
- Previous message: Richard Hollenbeck: "Re: append (INSERT INTO) query problems"
- In reply to: anonymous_at_discussions.microsoft.com: "Re: bug in access calling function from insert into query???"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
