Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: Query criteria based on Public Function

    I have a function that checks a configuration table for data.
    If the table contains a 'D' then the Function returns a 'D' but if the tablerow is empty then the Function returns ' A Or B Or C '.

    Now when I use the function as a Query Criteria everything works fine when the configuration table contains data but when it return data in form of ' A Or B Or C ' then the Query returns null, zero, nada...

    If I manually enter ' A Or B Or C ' in the search criteria then ewerything is ok.

    I also tried to have the function to return data in form of ' "A" Or "B" Or "C" ' but no luck...

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Your function is returning the string "'A' or 'B' or 'C'", so your query is searching for that entire string. It should work fine if the function returns a single value: "D".

    Unfortunately, I haven't figured out the solution yet.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Dec 2009
    Posts
    4
    Quote Originally Posted by jan-eric_enlund View Post
    I have a function that checks a configuration table for data.
    If the table contains a 'D' then the Function returns a 'D' but if the tablerow is empty then the Function returns ' A Or B Or C '.

    Now when I use the function as a Query Criteria everything works fine when the configuration table contains data but when it return data in form of ' A Or B Or C ' then the Query returns null, zero, nada...

    If I manually enter ' A Or B Or C ' in the search criteria then ewerything is ok.

    I also tried to have the function to return data in form of ' "A" Or "B" Or "C" ' but no luck...
    I have the exact same problem. When my function returns a specific value, say '54321' that record is selected. But when my function returns something like "not null" or "like *" where I'm trying to return all records, I get nothing.

    Were you able to resolve this?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    One possible reason is that the query optimizer would consider that the function is deterministic. In such a case, the function would be called only once instead of being called for each row.

    One way to prevent this is to pass the primary key of one table involved in the query to the function as a parameter, event if the function does not use it.
    Have a nice day!

  5. #5
    Join Date
    Dec 2009
    Posts
    4
    Quote Originally Posted by Sinndho View Post
    One possible reason is that the query optimizer would consider that the function is deterministic. In such a case, the function would be called only once instead of being called for each row.

    One way to prevent this is to pass the primary key of one table involved in the query to the function as a parameter, event if the function does not use it.
    Well, it turned out to be not so complicated.

    When my function returned something like '12345' the query correctly found the record with '12345' as the key.

    When my function returned something such as "like "*"" (which I wanted to return all records) instead, I got nada. Well, instead of interpreting "like "*" to indicate all records, the query did a search for a field with the value "like "*" in it (i.e., it searched for that string)

    To fix it I wrote a function that built an sql query statement via a querydef on the fly. That worked just fine.

    Thanks for taking the time to respond.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •