Welcome to SPN

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

Sign Up Now!

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

    >
    >
    >
     

Share This Page