Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

Problem with function in query

Discussion in 'Information Technology' started by Jürg Leuenberger, Nov 10, 2005.

  1. Jürg Leuenberger

    Jürg Leuenberger
    Expand Collapse
    Guest

    Hi all,
    I have a problem with my speed optimized code. Here is what's happening:
    - I have a function that looks up some default values in a table. Within
    this function I replaced the DLookup by the SQL-based RstLookup that was
    proposed to speed things up. This works just fine.

    - I call this function in an query to generate a calculated field. This
    works still perfectly as long as I run the query from the database window.

    - Now unfortunately I have to run the query from code. As soon as I do that,
    I get the error message "Invalid argument".

    - If I go and replace the RstLookup again with the DLookup in the said
    function, the code runs fine.

    Does anybody have an idea why that would be and how to avoid those troubles?
    Any help is greatly appreciated.

    George
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. [MVP] S.Clark

    [MVP] S.Clark
    Expand Collapse
    Guest

    1. Is the Procedure a Function, such that it can be called?
    2. Does the Function have parameters?
    3. Are parameters being passes to the function?
    4. Is there any chance that a Null is being passed to the function?
    5. Are any precautions being taken to prevent a Null from being passed to
    the function?

    --
    Steve Clark, Access MVP
    http://www.fmsinc.com/consulting
    *FREE* Access Tips: http://www.fmsinc.com/free/tips.html

    "Jürg Leuenberger" <JrgLeuenberger@discussions.microsoft.com> wrote in
    message news:D0476E5F-90CC-411B-BFB3-FD77C096F5A6@microsoft.com...
    > Hi all,
    > I have a problem with my speed optimized code. Here is what's happening:
    > - I have a function that looks up some default values in a table. Within
    > this function I replaced the DLookup by the SQL-based RstLookup that was
    > proposed to speed things up. This works just fine.
    >
    > - I call this function in an query to generate a calculated field. This
    > works still perfectly as long as I run the query from the database window.
    >
    > - Now unfortunately I have to run the query from code. As soon as I do
    > that,
    > I get the error message "Invalid argument".
    >
    > - If I go and replace the RstLookup again with the DLookup in the said
    > function, the code runs fine.
    >
    > Does anybody have an idea why that would be and how to avoid those
    > troubles?
    > Any help is greatly appreciated.
    >
    > George
     
  4. Jürg Leuenberger

    Jürg Leuenberger
    Expand Collapse
    Guest

    Thanks for going into this with me. Answering your questions:
    1) Yes, it is a function.
    2) Yes, it does have parameters.
    3) Yes, parameters are passe to the function.
    4) Not really, but you never know, so see 5)
    5) Yes, precautions are taken.

    The funny thing is that the same query containing the same function runs
    perfectly when I call it from the database window. When I call it from code
    however, it barfs at me.
    Even more ridiculous is the fact that when I step through the code, it
    actually goes to into the function (at the qry.OpenRecordset command),
    executes everything perfectly, the function returns the correct value .....
    and then gives me the error message (invalid argument).
    Is it possible that this has to do with calling an function containing a SQL
    query from within a query? But then why does it work when called from the DB
    window?

    thanks for your help,
    George

    "[MVP] S.Clark" wrote:

    > 1. Is the Procedure a Function, such that it can be called?
    > 2. Does the Function have parameters?
    > 3. Are parameters being passes to the function?
    > 4. Is there any chance that a Null is being passed to the function?
    > 5. Are any precautions being taken to prevent a Null from being passed to
    > the function?
    >
    > --
    > Steve Clark, Access MVP
    > http://www.fmsinc.com/consulting
    > *FREE* Access Tips: http://www.fmsinc.com/free/tips.html
    >
    > "Jürg Leuenberger" <JrgLeuenberger@discussions.microsoft.com> wrote in
    > message news:D0476E5F-90CC-411B-BFB3-FD77C096F5A6@microsoft.com...
    > > Hi all,
    > > I have a problem with my speed optimized code. Here is what's happening:
    > > - I have a function that looks up some default values in a table. Within
    > > this function I replaced the DLookup by the SQL-based RstLookup that was
    > > proposed to speed things up. This works just fine.
    > >
    > > - I call this function in an query to generate a calculated field. This
    > > works still perfectly as long as I run the query from the database window.
    > >
    > > - Now unfortunately I have to run the query from code. As soon as I do
    > > that,
    > > I get the error message "Invalid argument".
    > >
    > > - If I go and replace the RstLookup again with the DLookup in the said
    > > function, the code runs fine.
    > >
    > > Does anybody have an idea why that would be and how to avoid those
    > > troubles?
    > > Any help is greatly appreciated.
    > >
    > > George

    >
    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page