在SQL WHERE语句中使用组合框值 [英] Using combobox value within SQL WHERE statement

问题描述

您好，

我在Access 2013中创建了一个表格和表单，其中包含许多字段，包括组合框，以控制数据输入到特定列表。现在我希望用户能够搜索多个字段以查找特定信息，而我只使用
一个文本框来执行此操作。所以我创建了一个搜索字符串文本框和一个搜索按钮。

I have created a table and form in Access 2013 with a number of fields including combo boxes in order to control data entry to a specific list. Now I want the user to be able to search through multiple fields to find specific information and I'm using just one text box to do this from. So I created a search string text box and a search button.

我 使用VBA，因为我的Where语句非常长。 

I'm using VBA because my Where statement is very long. 

问题1 ：VBA代码适用于短文本字段和长文本字段，但不适用于组合框中的值。相反，它返回一个空白查询。我需要做些什么才能确定该字段是
SQL语句的SELECT或WHERE组件中的组合框？我有组合框值的单独表，一个包含所有字段的简单查询和一个表单。

Question 1: The VBA code works for short and long text field but not for values from combo boxes. Instead it returns a blank query. Is there something I need to do to identify that the field is a combo box within either the SELECT or WHERE component of the SQL statement? I have separate tables for the values of the combo boxes, a simple query that includes all fields, and one form.

问题2：我想将SQL查询的结果发送到位于ISS工作跟踪系统表单中的子表单/表，而不是单独的查询。

Question 2: I would like to send the results of the SQL query to a subform/table that resides within the ISS Work Tracking System form instead of to a separate query.

FYI：

• 该表名为数据
• 该表单名为 ISS工作跟踪系统
• 文本框，用于输入搜索条件被称为 txtSearchString

以下是代码：

Private Sub cmdSearch_Click（）

Dim SQLString As String

Dim qdf As QueryDef

Private Sub cmdSearch_Click()
Dim SQLString As String
Dim qdf As QueryDef

SQLString =" SELECT Data。[工作项目编号]，Data.Title，Data。[高级别描述]，数据。[详细说明]，Data.Owner，Data。[Activity log]，Data。[Activity date]，Data。[User name]，Data。[ISS] category]，Data。[Work type]，Data.Source，Data。[Business
process owner]，Data.Status，Da ta.Priority，Data。[立即关注]，数据。[立即关注优先]，数据。[预计发布日期]，数据。[Jira准备]，数据。[Jira数字]，数据。[Jira准备] ，数据。[ISERT提供]，数据。[e502准备]，
数据。[e502数字]，Data.Field1，Data.Field2，数据。[最后修改]，数据。[最后修改日期]，数据。[记录创建日期]" &安培; _
$
    "FROM Data" &安培; _
$
    " WHERE（（（Data.Title）Like"" *""& [Forms]！[ISS Work Tracking System]！[txtSearchString]&"" *""））OR （（（数据。[高级别描述]）类似于"" *""& [Forms]！[ISS
工作追踪系统]！[txtSearchString]&"" *" "））OR（（（Data.Owner）Like"" *"""& [Forms]！[ISS Work Tracking System]！[txtSearchString]&"" *""） ）（或（数据。[活动日志]）类似于"" *"""
& [Forms]！[ISS Work Tracking System]！[txtSearchString]&"" *" ;（））OR（（（数据。[国际空间站分类]）类似于"" *"""& [Forms]！[ISS工作跟踪系统]！[txtSearchString]&"" *" ;"）） 或（（（数据。[工作
类型]）类似于"" *""& [Forms]！[ISS工作追踪系统]！[txtSearchString]&"" *" "））OR（（（Data.Source）Like"" *""& [Forms]！[ISS Work Tracking System]！[txtSearchString]&"" *""） ）
OR（（（数据。[业务流程所有者]）喜欢"" *""& [Forms]！[ISS工作跟踪系统]！[txtSearchString]&"" * ""））OR（（（Data.Status）Like"" *""& [Forms]！[ISS Work Tracking System]！[txtSearchString]
&"" *""））" &安培; _
$
    " OR（（（Data.Priority）Like"" *""& [Forms]！[ISS Work Tracking System]！[txtSearchString]&"" *""））OR （（（数据。[工作项目编号]）类似于"" *"""& [Forms]！[ISS Work Tracking
System]！[txtSearchString]&"" *" "））;"

SQLString = "SELECT Data.[Work item number], Data.Title, Data.[High level description], Data.[Detailed description], Data.Owner, Data.[Activity log], Data.[Activity date], Data.[User name], Data.[ISS category], Data.[Work type], Data.Source, Data.[Business process owner], Data.Status, Data.Priority, Data.[For immediate attention], Data.[For immediate attention priority], Data.[Expected release date], Data.[Jira prepared], Data.[Jira number], Data.[Jira prepared on], Data.[ISERT provided], Data.[e502 prepared], Data.[e502 number], Data.Field1, Data.Field2, Data.[Last modified by], Data.[Last modified date], Data.[Record creation date]" & _
    "FROM Data " & _
    "WHERE (((Data.Title) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.[High level description]) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.Owner) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.[Activity log]) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.[ISS Category]) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*""))  OR (((Data.[Work type]) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.Source) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.[Business process owner]) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.Status) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*""))" & _
    "OR (((Data.Priority) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*"")) OR (((Data.[Work item number]) Like ""*"" & [Forms]![ISS Work Tracking System]![txtSearchString] & ""*""));"

On Error Resume Next

DoCmd.Close acQuery，" tempqry"

DoCmd.DeleteObject acQuery，"tempQry"和
On Error GoTo 0

设置qdf = CurrentDb.CreateQueryDef（" tempQry"，SQLString）

DoCmd。 OpenQuery（" tempQry"）

On Error Resume Next
DoCmd.Close acQuery, "tempqry"
DoCmd.DeleteObject acQuery, "tempQry"
On Error GoTo 0
Set qdf = CurrentDb.CreateQueryDef("tempQry", SQLString)
DoCmd.OpenQuery ("tempQry")

End Sub

我们非常感谢任何帮助 - 我对这类工作还不熟悉访问并观看了uTube上的无数视频来实现这一目标！

Any help would be greatly appreciated--I'm fairly new to this type of work in Access and have watched countless videos on uTube to get this far!

Jill

推荐答案

嗨吉尔，

对于问题＃1，你似乎成了
表格中查找字段的邪恶。

For Question #1, you seem to have fallen victim to The Evils of Lookup Field in Tables.

强烈建议不要在字段中设计带有组合框的表格。您可以考虑在解决搜索问题之前先解决这个问题。

It is highly discouraged to design tables with comboboxes in the fields. You might consider fixing this first before tackling your search problem.

只需要我的2美分......

Just my 2 cents...

这篇关于在SQL WHERE语句中使用组合框值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！