英文:
Querying for a partial value in a text field by using a control using a Like Expression in the Query
问题
我正在尝试让我的用户搜索一份记录列表,以查找包含特定医疗诊断的记录。涉及的文本字段可以包含多个诊断,但我通常只会搜索其中一个。这些是IDX代码,所以举个例子,我想能够输入“E11”来搜索,然后返回包含字段如“Z15.12、E11.1、D13.24”的记录。根据我的谷歌/堆栈搜索,我知道我需要使用LIKE表达式,并在之间包含它,但我似乎无法正确格式化它。我目前的工作表达式是
txtDxSearch: [tblQAChargesEntered]![Diagnosis] Like Nz([Forms]![MainMenu]![ManagersMenu]![txtDx],"*")
但当然这缺少前后的,并且只返回具有完全相同值的记录。有人可以帮助我正确格式化吗?只是添加*并尝试各种单引号和双引号都没有奏效,我也没有找到关于这个主题的谷歌/堆栈答案。
英文:
I am trying to allow my users to search a list of record for records that contain a particular medical diagnosis. The Text field in question can contain a number of diagnosis' but I'll usually only be search for one of them. These are IDX codes so as an example I'd like to be able to search for "E11" by typing that into a control on my form and have it return a record that contains field such as "Z15.12, E11.1, D13.24" From my google/stack searching I know I need to use a LIKE expression surrounded by * but I cant seem to get the formatting right. The expression I have working is
txtDxSearch: [tblQAChargesEntered]![Diagnosis] Like Nz([Forms]![MainMenu]![ManagersMenu]![txtDx],"*")
but of course that's missing the * at the front and back and only returns records with the exact value. Can anyone help me with the correct formatting. Just adding the * and trying a variety of single and double quotes hasn't worked and I haven't been able to find a google/stack answer on this topic.
答案1
得分: 1
在你的搜索文本前后添加通配符。这里有来自立即窗口的示例:
? "Z15.12, E11.1, D13.24" Like "*" & "E11" & "*"
True
然而,该模式也会匹配"E11"后面的其他数字:
? "E112" Like "*" & "E11" & "*"
True
如果不想要这样的匹配,请修改模式以排除它们:
? "E112" Like "*" & "E11" & "[!0-9]*"
False
在你的查询中,可以尝试如下方式:
tblQAChargesEntered.Diagnosis Like '*' & Forms!MainMenu!ManagersMenutxtDx & '[!0-9]*'
此外,这个Like
条件将排除tblQAChargesEntered.Diagnosis
为空的行。如果你希望它们包含在查询结果集中,请明确请求它们:
tblQAChargesEntered.Diagnosis Like '*' & Forms!MainMenu!ManagersMenutxtDx & '[!0-9]*'
OR tblQAChargesEntered.Diagnosis Is Null
英文:
Concatenate wild cards before and after your search text. Here's an example from the Immediate window:
? "Z15.12, E11.1, D13.24" Like "*" & "E11" & "*"
True
However, that pattern will also match "E11" plus another digit:
? "E112" Like "*" & "E11" & "*"
True
If you don't want such matches, revise the pattern to exclude them:
? "E112" Like "*" & "E11" & "[!0-9]*"
False
In your query, try it like this ...
tblQAChargesEntered.Diagnosis Like '*' & Forms!MainMenu!ManagersMenutxtDx & '[!0-9]*'
Also, that Like
condition will exclude rows where tblQAChargesEntered.Diagnosis
is Null. If you do want them included in your query result set, ask for them explicitly:
tblQAChargesEntered.Diagnosis Like '*' & Forms!MainMenu!ManagersMenutxtDx & '[!0-9]*'
OR tblQAChargesEntered.Diagnosis Is Null
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论