尝试在不使用vba的情况下针对查询功能量身定制查询 [英] Trying to tailor a query for search functionality without vba
问题描述
我正在尝试创建一个非常简单的搜索工具，以从表("ApplicationTable")中提取记录.向用户显示一个包含许多文本框控件的表单，每个文本框控件对应于表中的一个字段(ApplicationID，FirstName，LastName等).他们将数据输入到所需的任何字段中，然后单击一个按钮.搜索应返回其对应字段包含搜索参数的每条记录，并忽略任何空白的搜索字段.数据显示在弹出的报告中.
I'm trying to create a pretty bare-bones search tool to pull records from a table ("ApplicationTable"). The user is presented with a form containing a number of textbox controls, each one corresponding to a field in the table (ApplicationID, FirstName, LastName, etc.) They enter data into whatever field(s) they wish, then click a button. The search should return each record for which a corresponding field contains the search parameter, and ignores any search field that is blank. Data is displayed in a report that pops up.
例如，如果在ApplicationID控件中输入"A17"，它将返回ApplicationID字段包含"A17"的所有记录.如果在ApplicationID中输入"A17"，在FirstName中输入"John"，它将返回ApplicationID字段包含"A17"且FirstName字段包含"John"的所有记录.如果您在所有字段都为空的情况下单击搜索按钮，它将仅返回表中的所有记录.
For example, if you enter "A17" into the ApplicationID control, it'll return all records for which the ApplicationID field contains "A17". If you enter "A17" into ApplicationID and "John" into FirstName, it'll return all records for which the ApplicationID field contains "A17" and the FirstName field contains "John". If you hit the search button with all fields blank, it just returns all records in the table.
不幸的是，对于我一生来说，我无法通过正确的查询来使SQL成为正确的事情，因此我必须使用vba.但我不想这么做，因为它使很多事情变得比它需要的复杂. (例如，我试图将结果导出到Excel，这很麻烦，因为transferspreadsheet无法接受动态SQL.)
Unfortunately, for the life of me, I cannot get the SQL right to make this thing powered by a simple query, and so I'm having to use vba. But I don't want to, because it's making a lot of things more complicated than it needs to be. (e.g., I'm trying to export the results to Excel, which is a pain since transferspreadsheet can't accept dynamic SQL.)
所以！
这是我当前正在使用的vba代码:
Here is the vba code I'm using currently:
Dim QueryStr As String
Dim AddAnd As Boolean
AddAnd = False
If Not IsNull([Forms]![ClientSearchForm]![ApplicationID]) Then
QueryStr = QueryStr & "((ApplicationTable.ApplicationID) Like '*' &
[Forms]![ClientSearchForm]![ApplicationID] & '*')"
AddAnd = True
End If
If Not IsNull([Forms]![ClientSearchForm]![NevadaApplicationID]) Then
If AddAnd = True Then
QueryStr = QueryStr & " AND "
End If
QueryStr = QueryStr & "((ApplicationTable.NevadaApplicationID) Like '*'
& [Forms]![ClientSearchForm]![NevadaApplicationID] & '*')"
AddAnd = True
End If
If Not IsNull([Forms]![ClientSearchForm]![FirstName]) Then
If AddAnd = True Then
QueryStr = QueryStr & " AND "
End If
QueryStr = QueryStr & "((ApplicationTable.ClaimantFirstName) Like '*' &
[Forms]![ClientSearchForm]![FirstName] & '*')"
AddAnd = True
End If
If Not IsNull([Forms]![ClientSearchForm]![LastName]) Then
If AddAnd = True Then
QueryStr = QueryStr & " AND "
End If
QueryStr = QueryStr & "((ApplicationTable.ClaimantLastName) Like '*' &
[Forms]![ClientSearchForm]![LastName] & '*')"
AddAnd = True
End If
If Not IsNull([Forms]![ClientSearchForm]![AssignedStaff]) Then
If AddAnd = True Then
QueryStr = QueryStr & " AND "
End If
QueryStr = QueryStr & "((ApplicationTable.AssignedStaff) Like '*' &
[Forms]![ClientSearchForm]![AssignedStaff] & '*')"
AddAnd = True
End If
If Not IsNull([Forms]![ClientSearchForm]![VictimDescription]) Then
If AddAnd = True Then
QueryStr = QueryStr & " AND "
End If
QueryStr = QueryStr & "((ApplicationTable.VictimDescription) Like '*' &
[Forms]![ClientSearchForm]![VictimDescription] & '*')"
AddAnd = True
End If
DoCmd.OpenReport "ClientSearchQuery", acViewReport, , QueryStr
"ClientSearchQuery"是我正在使用的核心查询，上面没有任何过滤器:
"ClientSearchQuery" is the core query I'm using, without any filters on it:
SELECT ApplicationTable.PrimaryAppID, ApplicationTable.ApplicationID, ApplicationTable.NevadaApplicationID,
ApplicationTable.EarliestReceivedDate, ApplicationTable.ClaimantFirstName, ApplicationTable.ClaimantLastName,
ApplicationTable.AssignedStaff, ApplicationTable.ContactDate, ApplicationTable.VictimDescription
FROM ApplicationTable;
有帮助吗?我假设我需要使用iifs来使此功能仅通过SQL进行处理，但是我无法正确使用语法，否则它将抛出错误或返回错误的结果.
Help? I assume I need to use iifs to get this thing going with just SQL, but I couldn't get the syntax right, and it would either throw an error or return the wrong results.
推荐答案
要拯救的布尔逻辑！
像这样进行静态查询，您将不需要任何VBA.
Make your static query like this, and you won't need any VBA.
SELECT stuff
FROM ApplicationTable
WHERE (ApplicationID Like '*' & [Forms]![ClientSearchForm]![ApplicationID] & '*'
OR [Forms]![ClientSearchForm]![ApplicationID] IS NULL)
AND (NevadaApplicationID Like '*' & [Forms]![ClientSearchForm]![NevadaApplicationID] & '*'
OR [Forms]![ClientSearchForm]![NevadaApplicationID] IS NULL)
AND ...
对于每个AND子句，如果各自的搜索控件为NULL，则该子句始终为TRUE.
For each of the AND clauses, if the respective search control is NULL, then the clause is always TRUE.
注意:有关有效的VBA解决方案，请参见 http://allenbrowne.com/ser-62. html
Note: for a working VBA solution, see http://allenbrowne.com/ser-62.html
这篇关于尝试在不使用vba的情况下针对查询功能量身定制查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
