none
IIf Function RRS feed

  • Question

  • I am using the IIf Function to determine the criteria in a query. If it returns True there should be no criteria set. However if it returns false then a criteria should be set. Could someone help me to make this work. Thank you.

    Gordon Swan

    Sunday, January 21, 2018 4:42 PM

Answers

  • The best way to do this can't actually be built exactly in the visual query designer;  it would be to have a WHERE clause for the query's SQL like this:

    (tblYearClasses.StaffID=[TempVars]![User] OR [TempVars]![UserType]="Administrator") AND tblYearClasses.SchoolYearID=[Forms]![frmGradeEntry]![cboSchoolYear]

    If you want to build it in design view, you can either apply a criterion like this to the StaffID column:

        IIf([TempVars]![UserType]="Administrator",tblYearClasses.StaffID,[TempVars]![User])

    or else create a calculated column with this expression:

        (tblYearClasses.StaffID=[TempVars]![User]) OR ([TempVars]![UserType]="Administrator")

    and apply a criterion of True to it.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Gordon Swan Tuesday, January 23, 2018 1:13 AM
    Sunday, January 21, 2018 11:38 PM

All replies

  • Can you post what you have so far.  Please post your code thus far for us to review.

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Sunday, January 21, 2018 4:54 PM
  • The application is a school management system. If the person who logs in is the Administrator he should see all the classes. However if he is a tutor he should see only the classes he teaches. The person's UserType and StaffID are stored in the TempVars variable at login. I am including the query which shows the classes and the code from the login button.

    Sunday, January 21, 2018 5:24 PM
  • Instead of calling the IIF function use simple Boolean logic, e.g.

    PARAMETERS Forms!frmGradeEntry!cboSchoolYear LONG;
    SELECT YearClassID, ClassName, StaffID, YearClassName
    FROM tblClasses INNER JOIN tblYearClasses
    ON  tblClasses.ClassCode = tblYearClasses.ClassCode
    WHERE (TempVars("UserType") = "Administrator"
    OR StaffID = TempVars("User"))
    AND SchoolYearID = Forms!frmGradeEntry!cboSchoolYear;

    If the UserType variable's value is 'Administrator' the WHERE clause will evaluate to TRUE for every row, otherwise it will evaluate to TRUE only for those rows where the value of the User variable equals the value at the StaffID column position.  By parenthesizing the OR operation it evaluates independently of the AND operation.

    I've assumed that the Forms!frmGradeEntry!cboSchoolYear parameter is of long integer data type.  If not change the parameter declaration accordingly.

    Ken Sheridan, Stafford, England




    • Edited by Ken Sheridan Sunday, January 21, 2018 11:41 PM Typo corrected
    • Proposed as answer by Terry Xu - MSFT Monday, January 22, 2018 7:25 AM
    Sunday, January 21, 2018 11:36 PM
  • The best way to do this can't actually be built exactly in the visual query designer;  it would be to have a WHERE clause for the query's SQL like this:

    (tblYearClasses.StaffID=[TempVars]![User] OR [TempVars]![UserType]="Administrator") AND tblYearClasses.SchoolYearID=[Forms]![frmGradeEntry]![cboSchoolYear]

    If you want to build it in design view, you can either apply a criterion like this to the StaffID column:

        IIf([TempVars]![UserType]="Administrator",tblYearClasses.StaffID,[TempVars]![User])

    or else create a calculated column with this expression:

        (tblYearClasses.StaffID=[TempVars]![User]) OR ([TempVars]![UserType]="Administrator")

    and apply a criterion of True to it.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Gordon Swan Tuesday, January 23, 2018 1:13 AM
    Sunday, January 21, 2018 11:38 PM
  • Thanks for the assistance. I used this in Design View and it worked perfectly:

    If you want to build it in design view, you can either apply a criterion like this to the StaffID column:

    IIf([TempVars]![UserType]="Administrator",tblYearClasses.StaffID,[TempVars]![User])

    Tuesday, January 23, 2018 1:18 AM