Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Unanswered: Query Criteria If Statement Logic

    I am tying to set criteria for a query. I have a separate form with text boxes that act as controls for the criteria. In the query I have the criteria set as

    Like [Forms]![frm_PDoutput]![ItemNumberControl]

    Where item number control is the name of the text box on the form. I want to be able to set it to allow all if nothing is typed (because I will have more criteria boxes) so I had an If statement that was

    IIF(Forms]![frm_PDoutput]![ItemNumberControl] Is Not Null, Like [Forms]![frm_PDoutput]![ItemNumberControl])

    This should mean that if the box is blank return all if it has something in it then the criteria should be set to Like whatever is in the text box. But this does not seem to be working. Has anyone done anything similar to this? Also whenever I add the iif statement Access auto references Item Number to one of my tables within the criteria code and I am not sure why.

    The first like code does work just with the if statement added it fails to return any results.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Code:
    IIF(Forms]![frm_PDoutput]![ItemNumberControl] Is Not Null, Like [Forms]![frm_PDoutput]![ItemNumberControl])
    The main problem is that the IIF() function requires 3 arguements: the Boolean condition, the True part, and the False part. There is no such thing as a "default if otherwise." You have to tell Access what to do if the Boolean condition is False. Otherwise you will get an error message for not having sufficient arguements.

    Besides that, though, I've had problems using "Is Not Null". I sometimes get better results with Not IsNull(), so your IIF() would read
    Code:
    IIF(Not IsNull(Forms]![frm_PDoutput]![ItemNumberControl]), Like [Forms]![frm_PDoutput]![ItemNumberControl], <you provide a new false part>)
    Sam

  3. #3
    Join Date
    Mar 2012
    Posts
    2

    Reply

    I actually have iif statements that use just the true part. I have some criteria that is set so if true it sets the criteria if false it just keeps all records becauss thier is no false part set in the if statement.

    And I did try the last part but it still did not work.

    ADD:

    Found a post that may be a solution.

    http://www.dbforums.com/microsoft-ac...eft-blank.html
    Last edited by eforry; 03-21-12 at 08:41.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Please be aware that the IIF() statemnent in that posting has 1-both true and false parts, and 2-the Like operator on the outside of the IIF(), with "*" as the way it will dispay.

    Sam

  5. #5
    Join Date
    Jun 2014
    Posts
    1

    I think this might be your problem

    If you're using the GUI query builder in Access it puts your criteria expression in as a WHERE statement. here's an example of one that I built trying to do the same thing, but doesn't work:

    SELECT ['HMM-Chrgs-4-EPIC].*, Month([dateofentry]) & "/" & Day([dateofentry]) & "/" & Year([dateofentry]) AS [Date]
    FROM ['HMM-Chrgs-4-EPIC], [HMM Dataset Filters]
    WHERE ((['HMM-Chrgs-4-EPIC].[Pat-Status])=IIf([HMM Dataset Filters].[Patient Type]<>"ALL",[HMM Dataset Filters].[Patient Type]))

    Note the WHERE statement that defines the criteria:

    WHERE ((['HMM-Chrgs-4-EPIC].[Pat-Status])=IIf([HMM Dataset Filters].[Patient Type]<>"ALL",[HMM Dataset Filters].[Patient Type]))

    The IIF returns NULL if the field <> "ALL". The WHERE statement then says return values WHERE FIELD=NULL. That's not all values. It's just returning records where that field is null.

    If you rebuild this with
    1. "*" instead of NULL as your inclusive value and
    2. the Like statement outside of the IIF (not inside)
    then you should be able to make it work.

    Both pieces are critical. Here's what happens. Putting the LIKE outside of the IIF changes the WHERE FIELD = to WHERE FIELD LIKE. Changing the NULL to "*" says, if I return my inclusive value, match WHERE FIELD LIKE "*", which returns everything (except NULL; have to add another clause for that).

    Here is the SQL corrected and working:

    SELECT ['HMM-Chrgs-4-EPIC].*, Month([dateofentry]) & "/" & Day([dateofentry]) & "/" & Year([dateofentry]) AS [Date]
    FROM ['HMM-Chrgs-4-EPIC], [HMM Dataset Filters]
    WHERE (((['HMM-Chrgs-4-EPIC].[Pat-Status]) Like (IIf([HMM Dataset Filters].[Patient Type]<>"ALL",[HMM Dataset Filters].[Patient Type],"*"))) AND ((['HMM-Chrgs-4-EPIC].[Item #]) Is Not Null));

Tags for this Thread

Posting Permissions

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