使用查询中的Like表达式通过使用控件来查询文本字段中的部分值

huangapple go评论61阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年6月6日 01:44:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/76408823.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定