MS Access搜索表单WHERE子句含空字段时无法返回记录求助

你的问题出在当表单搜索控件为空时，原SQL用字段 Like "*"匹配，但Access中NULL Like "*"返回False，导致字段为空的记录被过滤；之前尝试的OR逻辑未区分“控件为空”和“字段为空”的场景，因此返回所有无关记录。

修改WHERE子句中每个条件的逻辑，给每个字段的搜索条件加上**“如果表单控件为空，则跳过该条件（不对字段做任何限制）”**的判断，结构为：
([Forms]![Copy Of 2200 - Manager Search]![控件名] Is Null OR [TABLE_Project_Tracking_Master].[字段名] Like "*" & [Forms]![Copy Of 2200 - Manager Search]![控件名] & "*")

修改后的完整SQL如下：

SELECT TABLE_Project_Tracking_Master.PRJ_Date_Live, TABLE_Project_Tracking_Master.PRJ_Date_Billing, TABLE_Project_Tracking_Master.PRJ_Date_Complete, TABLE_Project_Tracking_Master.PRJ_Date_Contract_Case, TABLE_Project_Tracking_Master.[PRJ_Case#], TABLE_Project_Tracking_Master.[PRJ_Contract#], TABLE_Project_Tracking_Master.[PRJ_Project#], TABLE_Project_Tracking_Master.PRJ_Core_Type, TABLE_Project_Tracking_Master.PRJ_Core_DC, TABLE_Project_Tracking_Master.PRJ_Core_Loc, TABLE_Project_Tracking_Master.[PRJ_Core_Bnk#], TABLE_Project_Tracking_Master.BNK_Cust_ID, TABLE_Project_Tracking_Master.BNK_Name, TABLE_Project_Tracking_Master.BNK_Name_Alt, TABLE_Project_Tracking_Master.BNK_City, TABLE_Project_Tracking_Master.BNK_ST, TABLE_Project_Tracking_Master.BNK_Assets, TABLE_Project_Tracking_Master.HRS_Mgmt, TABLE_Project_Tracking_Master.HRS_Project, TABLE_Project_Tracking_Master.HRS_Meeting, TABLE_Project_Tracking_Master.HRS_Prep, TABLE_Project_Tracking_Master.HRS_Training, TABLE_Project_Tracking_Master.HRS_Research, TABLE_Project_Tracking_Master.HRS_Test, TABLE_Project_Tracking_Master.HRS_BA_Live, TABLE_Project_Tracking_Master.HRS_PGM_Live, TABLE_Project_Tracking_Master.HRS_Support, TABLE_Project_Tracking_Master.HRS_Misc, TABLE_Project_Tracking_Master.HRS_INS_Total, TABLE_Project_Tracking_Master.HRS_Total, TABLE_Project_Tracking_Master.PRJ_Team, TABLE_Project_Tracking_Master.PRJ_Primary_BA, TABLE_Project_Tracking_Master.PRJ_Secondary_BA, TABLE_Project_Tracking_Master.PRJ_Primary_PGMR, TABLE_Project_Tracking_Master.PRJ_Secondary_PGMR, TABLE_Project_Tracking_Master.PRJ_Add_Resrc_1, TABLE_Project_Tracking_Master.PRJ_Add_Resrc_2, TABLE_Project_Tracking_Master.PRJ_Add_Resrc_3, TABLE_Project_Tracking_Master.CORE_Proj, TABLE_Project_Tracking_Master.CORE_PM, TABLE_Project_Tracking_Master.CORE_ASM, TABLE_Project_Tracking_Master.COMP_PM1, TABLE_Project_Tracking_Master.COMP_PM2, TABLE_Project_Tracking_Master.PRJ_Proj_Type, TABLE_Project_Tracking_Master.PRJ_Proj_Sub_Type, TABLE_Project_Tracking_Master.PRJ_Description, TABLE_Project_Tracking_Master.MGMT_Notes, TABLE_Project_Tracking_Master.MGMT_Desc_Of_Work, TABLE_Project_Tracking_Master.HII_OLIP, TABLE_Project_Tracking_Master.PRJ_OneNote, TABLE_Project_Tracking_Master.PRJ_FSCM, TABLE_Project_Tracking_Master.PRJ_Planner, TABLE_Project_Tracking_Master.AMT_000000_Pot_Earn, TABLE_Project_Tracking_Master.PRJ_Book_Revenue, TABLE_Project_Tracking_Master.PRJ_Book_Rev_Comp, TABLE_Project_Tracking_Master.PRJ_OneNote_Link, TABLE_Project_Tracking_Master.PRJ_Planner_Link, TABLE_Project_Tracking_Master.MGMT_Hold, TABLE_Project_Tracking_Master.HRS_Custom, TABLE_Project_Tracking_Master.PRJ_Cancelled, TABLE_Project_Tracking_Master.PRJ_Cancel_DT, TABLE_Project_Tracking_Master.PRJ_Waiting_on_Cust, TABLE_Project_Tracking_Master.HII_Waiting_On_ECS, TABLE_Project_Tracking_Master.PRJ_Closed_Mgmt, TABLE_Project_Tracking_Master.PRJ_On_Backlog, TABLE_Project_Tracking_Master.HII_ECS_Drop_Date, TABLE_Project_Tracking_Master.HRS_Development, TABLE_Project_Tracking_Master.HRS_No_Chrg_Custom, TABLE_Project_Tracking_Master.AUT_Ready, TABLE_Project_Tracking_Master.PRJ_Suite, TABLE_Project_Tracking_Master.ID, TABLE_Project_Tracking_Master.AMT_521000_IH_Install, TABLE_Project_Tracking_Master.AMT_521002_IH_Mrg, TABLE_Project_Tracking_Master.AMT_521005_OL_Disc, TABLE_Project_Tracking_Master.AMT_523000_CM_POD, TABLE_Project_Tracking_Master.AMT_524000_OL_Revenue, TABLE_Project_Tracking_Master.AMT_531000_Custom, TABLE_Project_Tracking_Master.AMT_531000_CSO, TABLE_Project_Tracking_Master.AMT_534000_Training, TABLE_Project_Tracking_Master.AMT_589606_Rev_xfer, TABLE_Project_Tracking_Master.AMT_799800_OL_Deferal, TABLE_Project_Tracking_Master.AMT_Bill_To, TABLE_Project_Tracking_Master.PRJ_Bill_To, TABLE_Project_Tracking_Master.AMT_Waive_Loss, TABLE_Project_Tracking_Master.PRJ_Waive_Loss_Reason, TABLE_Project_Tracking_Master.PRJ_Waive_Loss_Approver, TABLE_Project_Tracking_Master.PRJ_Status, TABLE_Project_Tracking_Master.AMT_000000_HII_Qtrly, TABLE_Project_Tracking_Master.HII_Hist_Retain, TABLE_Project_Tracking_Master.ID, TABLE_Project_Tracking_Master.REC_Date_Created, TABLE_Project_Tracking_Master.REC_User_Created
FROM TABLE_Project_Tracking_Master
WHERE 
    ([Forms]![Copy Of 2200 - Manager Search]![SrchLivDt] Is Null OR TABLE_Project_Tracking_Master.PRJ_Date_Live Like "*" & [Forms]![Copy Of 2200 - Manager Search]![SrchLivDt] & "*")
    AND ([Forms]![Copy Of 2200 - Manager Search]![SrchCase] Is Null OR TABLE_Project_Tracking_Master.[PRJ_Case#] Like "*" & [Forms]![Copy Of 2200 - Manager Search]![SrchCase] & "*")
    AND ([Forms]![Copy Of 2200 - Manager Search]![SrchProjNum] Is Null OR TABLE_Project_Tracking_Master.[PRJ_Project#] Like "*" & [Forms]![Copy Of 2200 - Manager Search]![SrchProjNum] & "*")
    AND ([Forms]![Copy Of 2200 - Manager Search]![SrchCmpID] Is Null OR TABLE_Project_Tracking_Master.BNK_Cust_ID Like "*" & [Forms]![Copy Of 2200 - Manager Search]![SrchCmpID] & "*")
    AND ([Forms]![Copy Of 2200 - Manager Search]![SrchBnkNam] Is Null OR TABLE_Project_Tracking_Master.BNK_Name Like "*" & [Forms]![Copy Of 2200 - Manager Search]![SrchBnkNam] & "*")
    AND ([Forms]![Copy Of 2200 - Manager Search]![SrchCity] Is Null OR TABLE_Project_Tracking_Master.BNK_City Like "*" & [Forms]![Copy Of 2200 - Manager Search]![SrchCity] & "*")
    AND ([Forms]![Copy Of 2200 - Manager Search]![SrchSt] Is Null OR TABLE_Project_Tracking_Master.BNK_ST Like "*" & [Forms]![Copy Of 2200 - Manager Search]![SrchSt] & "*")
    AND ([Forms]![Copy Of 2200 - Manager Search]![SrchTeam] Is Null OR TABLE_Project_Tracking_Master.PRJ_Team Like "*" & [Forms]![Copy Of 2200 - Manager Search]![SrchTeam] & "*")
    AND ([Forms]![Copy Of 2200 - Manager Search]![SrchPrmRes] Is Null OR TABLE_Project_Tracking_Master.PRJ_Primary_BA Like "*" & [Forms]![Copy Of 2200 - Manager Search]![SrchPrmRes] & "*")
    AND ([Forms]![Copy Of 2200 - Manager Search]![SrchPrmRes] Is Null OR TABLE_Project_Tracking_Master.PRJ_Primary_PGMR Like "*" & [Forms]![Copy Of 2200 - Manager Search]![SrchPrmRes] & "*")
    AND ([Forms]![Copy Of 2200 - Manager Search]![SrchPrjType] Is Null OR TABLE_Project_Tracking_Master.PRJ_Proj_Type Like "*" & [Forms]![Copy Of 2200 - Manager Search]![SrchPrjType] & "*")
    AND ([Forms]![Copy Of 2200 - Manager Search]![SrchPrjSubTyp] Is Null OR TABLE_Project_Tracking_Master.PRJ_Proj_Sub_Type Like "*" & [Forms]![Copy Of 2200 - Manager Search]![SrchPrjSubTyp] & "*")
ORDER BY TABLE_Project_Tracking_Master.PRJ_Date_Live DESC;

• 当某个表单搜索控件为空时，对应的条件[控件] Is Null为True，该条件不对字段做任何过滤，包含字段为空的记录。
• 当控件有输入值时，仅匹配字段值符合搜索内容的记录（如果需要同时匹配字段为空的场景，可以在每个OR后再加OR 字段 Is Null，比如([控件] Is Null OR 字段 Like "*" & [控件] & "*" OR 字段 Is Null)，但通常搜索有值时不需要匹配空记录）。

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