我有很多关键词需要过滤。 有没有办法使下面的查询更短?
我需要在下面找到包含关键关键字的字段。 只要我能做同样的事情,我就可以使用任何不同的方式。 我希望随着时间的推移有更多的关键词。
IIf([2CP].[commodity description] Like ('*SCRAP*')
Or [2CP].[commodity description] Like ('*PART*')
Or [2CP].[commodity description] Like ('*MIL*')
Or [2CP].[commodity description] Like ('*Material*')
Or [2CP].[commodity description] Like ('*PTS*')
Or [2CP].[commodity description] Like ('*SDDC*')
Or [2CP].[commodity description] Like ('*SEAT*')
Or [2CP].[commodity description] Like ('*FOOD*')
Or [2CP].[raw_cmd_desc] Like ('*SCRAP*')
Or [2CP].[raw_cmd_desc] Like ('*PART*')
Or [2CP].[raw_cmd_desc] Like ('*MIL*')
Or [2CP].[raw_cmd_desc] Like ('*Material*')
Or [2CP].[raw_cmd_desc] Like ('*PTS*')
Or [2CP].[raw_cmd_desc] Like ('*SDDC*')
Or [2CP].[raw_cmd_desc] Like ('*SEAT*')
Or [2CP].[raw_cmd_desc] Like ('*FOOD*')
Or [2CP].[raw_cmd_desc] Like ('*lorries motorcycles bicycles Foods*')
Or [2CP].[raw_cmd_desc] Like ('*lorries*')
Or [2CP].[shipper name] Like ('*SCRAP*')
Or [2CP].[shipper name] Like ('*PART*')
Or [2CP].[shipper name] Like ('*MIL*')
Or [2CP].[shipper name] Like ('*SDDC*')
Or [2CP].[shipper name] Like ('*FOOD*')
Or [2CP].[shipper name] Like ('*Hoegh*')
Or [2CP].[shipper name] Like ('*NYK*')
,1,0)
考虑一个 VBA 用户定义函数,该函数接收字段值作为参数,并循环遍历所有关键字(不带星号(的表列表。美妙之处在于,如果至少存在一个 True,则方法退出循环和函数,不需要检查所有其他值。
VBA功能(保存在标准模块中(
Public Function FindString(commodityParam As String, raw_cmdParam As String, shipperParam As String) As Boolean
Dim rst As Recordset
Dim tmp As Boolean
Set rst = CurrentDb.OpenRecordset("SELECT keyword FROM KeyWordsTable")
Do While Not rst.EOF
If commodityParam Like "*" & rst!keyword & "*" _
Or raw_cmdParam Like "*" & rst!keyword & "*" _
Or shipperParam Like "*" & rst!keyword & "*" Then
tmp = True
GoTo ExitFunction
End If
rst.MoveNext
Loop
ExitFunction:
rst.Close
Set rst = Nothing
TestLoop = tmp
End Function
SQL (VBA函数可用于任何SQL查询(
-- SELECT WITH CALCULATED COLUMN
SELECT *, FindString([commodity description], [raw_cmd_desc], [shipper name]) As Output
FROM [2CP];
-- SELECT WITH WHERE CONDITION
SELECT * FROM [2CP]
WHERE FindString([commodity description], [raw_cmd_desc], [shipper name]) = True;
-- APPEND QUERY
INSERT INTO (ID, BooleanValue)
SELECT ID, FindString([commodity description], [raw_cmd_desc], [shipper name])
FROM [2CP];
-- UPDATE QUERY
UPDATE [2CP]
SET BooleanValue = FindString([commodity description], [raw_cmd_desc], [shipper name]);
-- DELETE QUERY
DELETE FROM [2CP]
WHERE FindString([commodity description], [raw_cmd_desc], [shipper name]) = TRUE;