Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67

    IIf Criteria for Query

    Can someone tell me how to fix the Criteria for my query:



    IIf([Forms]![0001Test]![Status] Is Null, Like "*",[Forms]![0001Test]![Status])


    I want the report to show ALL if no status is chosen from the combo box on my "Choose Parameters for Report" form.

    Thanks,
    Al

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    IIF not needed.

    Like [Forms]![0001Test]![Status] & "*"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67

    Like [Forms]![0001Test]![Status] & "*"

    Hi,

    Thanks for your reply.

    When I use: Like [Forms]![0001Test]![Status] & "*" as suggested, I do not get all records in the query results.

    I have a test table I am using. It has 40 records. 10 records do not have a Status entered. When I use the above, I only get the 30 records that do have a status. I should get all records since the parameter field ([Forms]![0001Test]![Status]) in Form 0001Test was left blank.

    Please advise.

    Thanks in advance,
    Al

  4. #4
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67

    Got It

    This works:

    [Forms]![0001Test]![Status] OR [Forms]![0001Test]![Status] Is Null

    Thanks for everybody"s help.

    Al

    I SPOKE TOO SOON. DOESN'T WORK WELL EITHER.
    Last edited by alsoto; 10-03-2011 at 03:07 PM.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Yes, I did not think about possible Nulls. The wildcard should work if every record has a value.

    Your solution means the report will return records that meet the specific criteria as well as records that are null in that field but not ALL records. Is this satisfactory?

    This should return ALL:
    [Forms]![0001Test]![Status] LIKE "*" OR [Forms]![0001Test]![Status] Is Null

    Is Status a text datatype, not a Yes/No?
    Last edited by June7; 10-03-2011 at 04:51 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67

    Data is Text

    Report should show either records that match value entered in parameter form (1, 2, or all 3 different parameters) or All records, whether they are null or not null.

    Please advise.
    Thanks

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Only 3 ways I can see.

    One is to always require a value in the field, even if it is NA for not available. This could be a default setting for the field so when new record created it is populated.

    Another is to use expression in a query to give all the Null status records a value, such as NA or empty string, and then use that constructed field as the criteria field. Such as - AllStatus: Nz([Status],"")

    I don't normally use parameters in queries. I use VBA code to construct the filter criteria for a form or report and pass it to the form/report with the WHERE argument of DoCmd.OpenReport or OpenForm.

    What is the datatype of Status field?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67

    Using Code would be preferred

    I have 3 parameters:

    Status (text)
    Rep (text)
    Program (text)

    plus a Between AND date range.
    StartD and EndD

    I thought about DoCmd.OpenReport, but won't OpenArgs only take one parameter? I would love to use code instead of query parameters, can you give me an example?

    Thanks,
    Al

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Okay, here is from my project that will construct a WHERE clause to filter data (OpenArgs serves another purpose) from selected criteria. Only the AND operator is used, no OR.

    DoCmd.OpenForm "DataSoilsAggMoistOrg", acNormal, , "Submit.DateEnter=#" & Forms![SampleManagement]!ctrSampleList!DateEnter & "#" & _
    " AND Submit.ProjRecID=" & Forms![SampleManagement]!ctrSampleList!ProjRecID, , , "Edit" & rsGroup.RecordCount

    Yours would look something like:
    DoCmd.OpenReport "reportname", , ,"Status='" Me.txtStatus & "' AND Rep='" & Me.txtRep & "' AND Program='" & Me.txtProgram & "' AND #" & Me.Date & "# BETWEEN StartD AND EndD"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67
    June7,

    Can you PLEASE add Sort By (parameter field called [SortBy])? Then I can test it.

    THANK YOU SO MUCH FOR YOUR HELP WITH THIS!!!
    Al

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    The sort criteria is not passed in the WhereCondition argument of OpenReport. Set sort in the report's RecordSource or in Grouping&Sorting settings or in the report OrderBy property.

    See http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67
    Ok, thanks. I will let you know how it goes.

    Thanks,
    Al

  13. #13
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67

    Syntax has an error - red in VB window

    DoCmd.OpenReport "0001RptSummary", , ,"Status='" Me.Status & "' AND Rep='" & Me.Rep & "' AND Program='" & Me.Program & "' AND #" & Me.StartDate & "# BETWEEN StartD AND EndD"

    With this code, if the field happens to be empty (ie no rep chosen on parameter form), will report show all?

    Thanks,
    Al

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    No, allowing for nulls is another issue.
    Try:
    DoCmd.OpenReport "0001RptSummary", , ,"Status LIKE '" Me.Status & "*' AND Rep LIKE '" & Me.Rep & "*' AND Program LIKE '" & Me.Program & "*' AND #" & Me.StartDate & "# BETWEEN StartD AND EndD"

    If no dates given, that may be trickier. An alternative is to build the criteria string incrementally. Example:
    strSQL = IIf(IsNull(.cbxField1) Or (Not IsNull(.cbxField1) And IsNull(.cbxFor1)), "", strField1 & "='" & strFor1 & "'")
    strSQL = strSQL & IIf(IsNull(.cbxField2) Or (Not IsNull(.cbxField2) And IsNull(.cbxFor2)), "", IIf(strSQL = "", "", " AND ") & strField2 & "='" & strFor2 & "'")
    strSQL = strSQL & IIf(IsNull(.cbxCategory) Or (Not IsNull(.cbxCategory) And (IsNull(.tbxStart) Or IsNull(.tbxEnd))), "", IIf(strSQL = "", "", " AND ") & strField & " BETWEEN " & strStart & " AND " & strEnd)
    strSQL = strSQL & IIf(IsNull(.cbxTest), "", IIf(strSQL = "", "", " AND ") & "TestNum='" & .cbxTest & "'")
    Last edited by June7; 10-04-2011 at 08:41 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67

    There will always be dates

    LOL - looks complicated. Beautiful looking code, just over my head.

    There will ALWAYS be dates. Does that make it easier?

    Al

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-13-2011, 11:00 AM
  2. query criteria
    By combine21 in forum Queries
    Replies: 1
    Last Post: 11-08-2010, 12:21 PM
  3. Query Criteria
    By combine21 in forum Queries
    Replies: 12
    Last Post: 09-08-2010, 01:06 PM
  4. Query Criteria
    By MrMitch in forum Queries
    Replies: 0
    Last Post: 03-31-2009, 02:25 PM
  5. Query Criteria
    By jena in forum Queries
    Replies: 1
    Last Post: 04-29-2008, 11:00 AM

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