Results 1 to 15 of 15

Thread: IIF in a query

  1. #1
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Unanswered: IIF in a query

    In the desigen of a Query, can I use something like that in the criteria of one filed

    IIF(Forms!F_Main.Chk_StichTag=TRUE, get_StichTag())

    Where get_StichTag is a a function returns today's date or any date you can set in a text box through a global variable....

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by hammbakka
    In the desigen of a Query, can I use something like that in the criteria of one filed

    IIF(Forms!F_Main.Chk_StichTag=TRUE, get_StichTag())

    Where get_StichTag is a a function returns today's date or any date you can set in a text box through a global variable....
    Hammy,

    And your point is???? Where's the question or are you putting this out for a FYI for the programming challenged? ;D
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Quote Originally Posted by hammbakka
    In the desigen of a Query, can I use something like that in the criteria of one filed

    IIF(Forms!F_Main.Chk_StichTag=TRUE, >= get_StichTag())

    Where get_StichTag is a a function returns today's date or any date you can set in a text box through a global variable....


    Hello Owen,
    No Challenges

    When I run the query usig this IIF it returens no data, but when I put only the true part of the IIF which is [ >=get_StichTag() ] it functions and returns the required data.

    In the original post I forgot the >=

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    you can't return another comparision in the result section of the if statement.

    That's basically reading:

    If Chk_StichTag is true, then greater then or equal to get_STichTag()

    That doesn't make any sense.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Thanks,
    do you have any Idea to go arround this? So that I can use a comparison sigen?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What do you need to do exactly? Do you need to use two comparisons? You can link them with AND just like anywhere else if need be... I don't understand what you need for a result..?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Mar 2004
    Posts
    82
    My 2c...
    The IIF fn takes three args, so put your >=getSt in the first with an AND to what you have already. Your second arg is as you had it (ie, return the value); the 3d would be if it does not meet the evaluation.
    J.

  8. #8
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Post

    Quote Originally Posted by Teddy
    What do you need to do exactly? Do you need to use two comparisons? You can link them with AND just like anywhere else if need be... I don't understand what you need for a result..?
    I have a check box on a form, if it is checked, I would like to filter the data according to today's date through the function Get_StichTag() .
    If the check box is not checked, I would like not to filter the data.

    I could make two queries, one with creteria to filter the data, the other without then call the suitable one according to the status of the check box.

    My question, is it possible to use one Query and use the IIF to determine whether to use a creteria or not.

    Hope it is clear now.
    Thanks

  9. #9
    Join Date
    Nov 2002
    Posts
    272
    You can rephrase your condition to
    .... AND (<date condition> OR <checkbox is unchecked>) ...
    If the checkbox is unchecked, the expression in the brackets is true. If it's checked, the date condition applies.

  10. #10
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Quote Originally Posted by ivon
    You can rephrase your condition to
    .... AND (<date condition> OR <checkbox is unchecked>) ...
    If the checkbox is unchecked, the expression in the brackets is true. If it's checked, the date condition applies.
    I think the problem is, IIF evaluates the expression as text and not as function, so, in the creteria it will be filted according to ">= Get_StichTag()" and not filtered as a result of the function >= Get_StichTag()

  11. #11
    Join Date
    Nov 2002
    Posts
    272
    If you really want to use IIF, you shoud do something like
    ...AND IIF(<checkbox checked>, field >= Get_StichTag(), True) ...

  12. #12
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Here's my 2c:

    Put a user defined function in to do exactly what you want.

    In the query, put "=fnEvalDate()"

    In a public module, put something along the lines of:
    Code:
    Public Function fnEvalDate() As String
        If Forms!F_Main.Chk_StichTag Then
            fnEvalDate = "#" & Format(get_StichTag,"mm/dd/yyyy") & "#"
            ' This assumes get_StichTag returns a date value.
            ' If it returns text, use this:
            fnEvalDate = get_StichTag
        Else
            fnEvalDate = "*"
        End If
    End Function
    As a general rule of performance, you want to avoid using functions in Queries if possible, and if not possible, use Module based functions instead of writing the function directly in the QBE grid. In this case, it's basically a 2 for 1: Using the Iif in the query along with get_StichTag() requires 2 compilations per record. Using 1 user defined function required 1 compilation per record plus 1 compilation of the module per executaion of the query. So, if the datasource was large, and/or the PC was slow, the module based function would run faster.

    Have fun
    Last edited by tcace; 05-28-04 at 10:24.

  13. #13
    Join Date
    Nov 2002
    Posts
    272
    tcace:

    If we use
    .... AND (<date field> >= p_StichTag OR p_CheckBoxState) ...
    and use p_StichTag and p_CheckBoxState as parameters; would that be even faster? Function get_StichTag would need only 1 execution; just before the query runs.

  14. #14
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    There's a line somewhere between performance and style.

    As a developer, an additional reason I like to use module based functions instead of bulding expressions in the QBE grid is "debugging". If there is a problem with any part of your expression (or worse, it doesn't error, but gives you the wrong result) you get very little information to work with (it is all or nothing). If you have several queries stacked together feeding into a form and one of your equations fails, the error almost NEVER shows up at the actual problem (the error pops up down stream). By using a module based function, it's easier to write, possible to watch, easier to change and very visible. Using a module based function, the error pops up in the module and you can see it, plain as day on your screen.

    As for performance, it still has to compile the expression "AND (<date field> >= p_StichTag OR p_CheckBoxState) " on each record which includes several functions if placed in the QBE grid. I don't know if it re-evaluates the p_StichTag and p_CheckBoxState each time it recompiles the expression (my guess would be that it does).

    An alternate to using modules while still avoiding the multi-function expression in the query is to put a hidden text box on your form and setup (through code or the recordsource property) the textbox to equal the date you want to filter by.

    So, let's say the hidden control is Text1, your check box is Check1 and the date we're filtering by is in Text2. The recordsource for Text1 would read:
    Code:
    =Iif([Check1],"#" & Format([Text2],"mm/dd/yyyy") & "#","*")
    and the filter in your query would be:
    Code:
    =Forms!Form1!Text1
    This would be the best performance wise: the expression compiles once and calculates once - both of which happen before the query is executed. The query simply sucks the value in from the form.

    Note that the performance side of this only comes into play with some combination of the following: very large sets of data, very busy or poor network connections, very old PC's, and sluggish data sources (like ODBC).

    Have fun

  15. #15
    Join Date
    Nov 2002
    Posts
    272

    Thumbs up

    Thanks for the info, and your time.

    Performance has never been an issue with the Access apps I maintain, but I do want to learn about these things.

Posting Permissions

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