MS Access中基于表单组合框筛选空记录的查询条件失效问题求助

Let’s break down what’s going wrong with your current code and get you the correct solution for your requirement.

First, Fix the Syntax Errors in Your Original Code #

Your current expression has a few critical issues that prevent it from working:

1. Access uses IIf, not If: In Access queries, inline conditional logic uses the IIf function (not the VBA If statement).
2. Invalid Nz syntax: Your Nz call has misplaced quotes around the field name, and Nz requires two arguments (the value to check, and what to return if it’s null).
3. Invalid Like"": The syntax is missing a space (Like ""), but this also isn’t the right approach for your goal.

Solution Based on Your Requirement #

You stated: When Combo95 is "No", show empty records (I’ll cover two common interpretations of this, since "empty records" can mean two things):

Option 1: Show NO records at all when Combo95 is "No" #

If you want the query to return zero records when the combo is set to "No", add this condition to your query’s WHERE clause:

[Forms]![Statusfrm]![Combo95] = "Yes"

• When Combo95 is "Yes", this condition passes, and your existing filters apply normally.
• When Combo95 is "No", this condition fails, so no records are returned.

Option 2: Show records where [IMNotifications].[Order] is empty (null/blank) when Combo95 is "No" #

If "empty records" means records where the Order field is null or blank, use this in your query’s WHERE clause:

([Forms]![Statusfrm]![Combo95] = "Yes" AND [IMNotifications].[Order] Is Not Null) 
OR 
([Forms]![Statusfrm]![Combo95] = "No" AND ([IMNotifications].[Order] Is Null OR [IMNotifications].[Order] = ""))

• This checks for non-empty Order values when Combo95 is "Yes", and empty (null/blank) values when it’s "No".

Additional Checks #

• Make sure your combo box returns exactly "Yes" or "No" (no extra spaces). Verify this by checking the combo’s Row Source property.
• If you want to handle cases where the combo box is unselected (null), add OR [Forms]![Statusfrm]![Combo95] Is Null to the relevant part of the condition (e.g., to show all records when the combo is unselected).

内容的提问来源于stack exchange，提问作者Jacob