Access SQL查询带前导零的小值设备编号匹配失败问题求助

解决方案 #

你只需要把SQL中设备编号的匹配条件替换为兼容前导零的逻辑即可，不需要修改表结构，同时可以保留所有字段的模糊下钻能力。

修改逻辑说明 #

原来的设备编号匹配规则仅支持文本模糊匹配，无法适配输入不带前导零的数字和数据库存储带前导零文本的匹配场景。我们可以借助Access内置的VAL()函数解决这个问题：

• VAL()函数会自动忽略文本前导零，将文本开头的数字部分转为数值，比如VAL("001")和VAL("1")都会得到数值1
• 保留原有模糊匹配逻辑，同时增加空值判断，设备搜索框为空时不会限制其他字段的筛选，完全不影响工单下钻能力

需要替换的条件 #

把原来的

((TBTask.EquipID) Like "*" & [Forms]![SEARCH]![SearchEquipment] & "*")

替换为

(
  Nz([Forms]![SEARCH]![SearchEquipment],"")="" 
  OR 
  TBTask.EquipID Like "*" & [Forms]![SEARCH]![SearchEquipment] & "*" 
  OR 
  VAL(TBTask.EquipID)=VAL(Nz([Forms]![SEARCH]![SearchEquipment],0))
)

修改后完整SQL #

SELECT TBEquipment.[Equipment No], TBEquipment.[Equipment Name], TBTask.*
FROM TBEquipment INNER JOIN TBTask ON TBEquipment.[Equipment No] = TBTask.EquipID
WHERE (((TBTask.CLOSED) Like "*" & [Forms]![SEARCH]![SearchClosed] & "*" Or (TBTask.CLOSED) Is Null) 
AND (
  Nz([Forms]![SEARCH]![SearchEquipment],"")="" 
  OR 
  TBTask.EquipID Like "*" & [Forms]![SEARCH]![SearchEquipment] & "*" 
  OR 
  VAL(TBTask.EquipID)=VAL(Nz([Forms]![SEARCH]![SearchEquipment],0))
)
AND ((TBTask.ID) Like "*" & [Forms]![SEARCH]![SearchWO] & "*") 
AND ((TBTask.Opened) Like "*" & [Forms]![SEARCH]![SearchOpened] & "*") 
AND (((TBTask.Description) Like "*" & [Forms]![SEARCH]![SearchKeyword1] & "*" Or (TBTask.Description) Is Null) 
And ((TBTask.Description) Like "*" & [Forms]![SEARCH]![SearchKeyword2] & "*" Or (TBTask.Description) Is Null) 
And ((TBTask.Description) Like "*" & [Forms]![SEARCH]![SearchKeyword3] & "*" Or (TBTask.Description) Is Null) 
And ((TBTask.Description) Like "*" & [Forms]![SEARCH]![SearchKeyword4] & "*" Or (TBTask.Description) Is Null)) 
AND (((TBTask.Solution) Like "*" & [Forms]![SEARCH]![SearchKeyword5] & "*" Or (TBTask.Solution) Is Null) 
And ((TBTask.Solution) Like "*" & [Forms]![SEARCH]![SearchKeyword6] & "*" Or (TBTask.Solution) Is Null) 
And ((TBTask.Solution) Like "*" & [Forms]![SEARCH]![SearchKeyword7] & "*" Or (TBTask.Solution) Is Null) 
And ((TBTask.Solution) Like "*" & [Forms]![SEARCH]![SearchKeyword8] & "*" Or (TBTask.Solution) Is Null)) 
AND ((TBTask.PartsToOrder) Like "*" & [Forms]![SEARCH]![SearchParts] & "*" Or (TBTask.PartsToOrder) Is Null) 
AND ((TBTask.PartsRequest) Like "*" & [Forms]![SEARCH]![SearchPartsRequested] & "*") 
AND ((TBTask.PartsComplete) Like "*" & [Forms]![SEARCH]![SearchPartsOrdered] & "*") 
AND ((TBTask.Review) Like "*" & [Forms]![SEARCH]![SearchReview] & "*") 
AND ((TBTask.SafteyIssue) Like "*" & [Forms]![SEARCH]![SearchSafety] & "*") 
AND ((TBTask.Mgr_Approved) Like "*" & [Forms]![SEARCH]![SearchMgrAP] & "*") 
AND ((TBTask.Status) Like "*" & [Forms]![SEARCH]![SearchStatus] & "*")) 
OR (((Nz([Forms]![Search]![SearchWO],""))="") 
AND ((Nz([Forms]![Search]![SearchEquipment],""))="") 
AND ((Nz([Forms]![Search]![SearchOpened],""))="") 
AND ((Nz([Forms]![Search]![SearchClosed],""))="") 
AND ((Nz([Forms]![Search]![SearchKeyword1],""))="") 
AND ((Nz([Forms]![Search]![SearchKeyword2],""))="") 
AND ((Nz([Forms]![Search]![SearchKeyword3],""))="") 
AND ((Nz([Forms]![Search]![SearchKeyword4],""))="") 
AND ((Nz([Forms]![Search]![SearchKeyword5],""))="") 
AND ((Nz([Forms]![Search]![SearchKeyword6],""))="") 
AND ((Nz([Forms]![Search]![SearchKeyword7],""))="") 
AND ((Nz([Forms]![Search]![SearchKeyword8],""))="") 
AND ((Nz([Forms]![Search]![SearchParts],""))="") 
AND ((Nz([Forms]![Search]![SearchPartsRequested],""))="") 
AND ((Nz([Forms]![Search]![SearchPartsOrdered],""))="") 
AND ((Nz([Forms]![Search]![SearchReview],""))="") 
AND ((Nz([Forms]![Search]![SearchSafety],""))="") 
AND ((Nz([Forms]![Search]![SearchMgrAP],""))="") 
AND ((Nz([Forms]![Search]![SearchStatus],""))=""))
ORDER BY TBTask.CLOSED DESC;

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