Results 1 to 6 of 6
  1. #1
    jtm013 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2014
    Posts
    117

    Help with IIF

    Hello,

    I have a query which gives me a list of tools currently due for calibration each month.
    The query filters out tools which are not actively in use.
    However, there is a subset of tools which even when not actively in use should still be shown as due for calibration.



    I am no good with the sql for queries but hopefully I can explain from the design view screen well enough.

    The field that determines if a tool should be on the list is called [toolIssued]. Generally, in the criteria section I have it set to show all tools due this month for whom the field [toolIssued] <>0. However, in order to include the specific subset of tools (called Dies) I have tried to use the following in the criteria section:

    IIF(0, IIF([ToolDescription]= Like "*" & [Die] & "*") However, this returns an invalid syntax error. I have also tried:
    IIF(= 0, IIF([ToolDescription]= Like "*" & [Die] & "*")
    IIF(0, IIF([ToolDescription]= Like "*" & [Die] & "*",),)

    However, both of those options also return invalid syntax errors.

    Thank you for any and all assistance.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    jtm013 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2014
    Posts
    117
    Thanks for the link.

    I have managed to get the query to run. However, I am still messing something up.
    I now have the following in the criteria section:
    iif([toolissued]=0,iif([tooldescription] like "*" & [Die] & "*",","))

    When I run the query I get a pop-up input box that says "Die" and requests input.

    All I want is for query to display a list of tools which are due this month (that section of the query currently works fine) limited to tools that are issued (I.E. <> 0 in the [toolissued] field) or have the word "Die" in the field [tooldescription].

  4. #4
    jtm013 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2014
    Posts
    117
    Nevermind...I was trying to make things way to complicated.

    I ended up with a Criteria of <>0 and then just put the [ToolDescription] Like "*" & Die & "*" in the OR section.
    Works great now. Thanks for the assist though!

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not sure I am following your post, but it seems you have 2 field conditions:
    <> in toolissued field OR
    have the string 'die' in the tooldescription field

    iif(toolissued <> 0 or tooldescription like "*die*", true part here, false here)

    It would be helpful to readers if you would show us all of your code in context.

    "criteria section" sounds like you're describing the query design grid. You would not need the iif in that case, if I'm understanding correctly.

  6. #6
    jtm013 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2014
    Posts
    117
    Orange- You are correct I was describing the query design grid.

    I realized the iif was unnecessary.

    The final arrangement of the query grid is shown below.
    In this case the field [toolissued] is a number field where 0 indicates not issued.
    As mentioned above the subset of tools which needed to be included, despite not being issued are called Dies.
    So that is what is being looked for in the field [tooldescription]

    Field: toolID toolIssued Year([toolNextCalDate])*12+DatePart("m",[toolNextCalDate]) ToolDescription
    Table: tblDataPrime tblDataPrime tblDataPrime
    Sort:
    Show: Yes No No Yes
    Criteria: <>0 Year(Date())*12+DatePart("m",Date())
    OR: Year(Date())*12+DatePart("m",Date()) Like "*" & "Die" & "*"

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums