将信息从表单传递到查询作为条件 [英] Pass information from a form to a query as criteria
问题描述
我创建了一个查询，它将从数据库中为运算符提取信息。此查询将平均提取50,000条记录。运营商需要优化搜索结果。我在查询的条件部分使用了以下内容，以便运算符优化搜索[输入客户端名称：]。但是，操作员并不总是记住客户端名称在数据库中的显示方式，并且我们会收到几个失败的查询。为了纠正这个问题，我创建了一个带有选择列表和命令按钮的表单。操作员将选择客户端名称并从命令按钮运行查询。在查询中，我用以下内容替换了[输入客户名称：];形式！Form1中！文本1。表单中text1中的选定值将传递给查询，并返回结果。这会将结果缩小到2000条记录。但是，我们仍然需要进一步优化搜索。查询中的一个字段存储分支编号。表中的字段是文本字段。运营商需要从结果中排除某组分支。在表单上，我创建了一个文本框，供他们输入要排除的分支。我让他们进入
Not In（59，99，78）。当它传递给查询时，Access会在文本周围放置单个''，并且查询返回0条记录。如果Not In语句直接放在查询条件字段中，我们会得到正确的结果。如何将这种类型的信息从表单传递给查询，以便我们可以优化结果？
如果您告诉我们您是如何构建SQL的（或查询）以及你如何应用过滤器我们可以帮助你修改版本。
你的问题并没有说清楚你在做什么。
如果您告诉我们您是如何构建SQL（或查询）以及如何应用过滤器我们可以帮助您修改后的版本。
你的问题并没有说清楚你在做什么。
这是一个简单的查询，它基于一个包含15列数据的表。 （14列是基于文本的，1是日期）。我正在检索所有15列数据。我目前正在过滤两列（日期和名称），并希望添加第三列（分支＃）。分支编号从00到99并再次存储为文本
在表单（标题为活动）上，操作员将在文本框中输入日期（标题为日期）。在单独的文本框中，操作员将输入客户端名称。 （标题名称）。在查询中，日期列标准具有以下内容 -
表格！活动！日期。 name列具有以下 - forms！activity！name。
当运算符从表单启动查询时，它将日期和名称传递给查询，我们得到结果。我想为分支编号添加一个过滤器。我们需要从结果中排除某些分支编号以获取我们正在寻找的数据。
我试过以下但没有运气。我在表单上创建了一个新的文本框（标题分支）。在查询中，分支列标准具有以下内容 -
表单！活动！分支。我已尝试传递以下内容来过滤分支编号（请记住，分支编号列存储为文本）
Not In（" 59"，" 99" ）
<> " 59"和<> " 99"
如果从表单中使用上述任何一个过滤器，我们会返回0个项目。
我发现Access在上面的过滤器周围放置单个''，这就是为什么我相信我们得到了0条记录。如果我在查询条件字段中直接使用上面的过滤器，我们会得到正确的结果。有没有其他方法来过滤将返回结果的分支编号？
只是想知道...为什么数字存储为文本？您可能想尝试将＃59＃放在数字周围。并且也可能想尝试为计算的数字设置一个参数作为文本而不是数字。
只是一个想法。
这里是参数外观的sql：
PARAMETERS表格！活动！分支文本（255）;
SELECT（你的选择条款）
FROM（等等）
WHERE（等等......）
这是一个简单的查询它基于一个包含15列数据的表。 （14列是基于文本的，1是日期）。我正在检索所有15列数据。我目前正在过滤两列（日期和名称），并希望添加第三列（分支＃）。分支编号从00到99并再次存储为文本
在表单（标题为活动）上，操作员将在文本框中输入日期（标题为日期）。在单独的文本框中，操作员将输入客户端名称。 （标题名称）。在查询中，日期列标准具有以下内容 -
表格！活动！日期。 name列具有以下 - forms！activity！name。
当运算符从表单启动查询时，它将日期和名称传递给查询，我们得到结果。我想为分支编号添加一个过滤器。我们需要从结果中排除某些分支编号以获取我们正在寻找的数据。
我试过以下但没有运气。我在表单上创建了一个新的文本框（标题分支）。在查询中，分支列标准具有以下内容 -
表单！活动！分支。我已尝试传递以下内容来过滤分支编号（请记住，分支编号列存储为文本）
Not In（" 59"，" 99" ）
<> " 59"和<> " 99"
如果从表单中使用上述任何一个过滤器，我们会返回0个项目。
我发现Access在上面的过滤器周围放置单个''，这就是为什么我相信我们得到了0条记录。如果我在查询条件字段中直接使用上面的过滤器，我们会得到正确的结果。是否有另一种方法可以过滤返回结果的分支编号？
I have created a query that will pull information from our database for the operators. This query will pull in on average 50,000 records. The operators need to refine the search results. I have used the following in the criteria section of the query for the operators to refine the search [Enter the Clients Name:]. However, the operators do not always remember how the clients name appears in the DB and we get several failed queries. To correct this, I created a form with a pick list and a command button. The operator will select the clients name and run the query from the command button. In the query I replaced [Enter the Clients Name:] with the following; forms!form1!text1. The selected value in text1 from the form gets passed to the query and the results are returned. This will shrink the results to maybe 2000 records. However, we still need to refine the search even further. One of the fields in the query stores branch numbers. The field in the table is a text field. The operator needs to exclude a certain group of branches from the results. On the form I created a text box for them to enter the branches to exclude. I have them entering
Not In ("59","99","78"). When this gets passed to the query, Access puts single '' around the text and the query returns 0 records. If the Not In statement is placed directly in the query criteria field we get the proper results. How can I pass this type of information from a form to a query so we can refine the results?
If you tell us how you''re building your SQL (or query) and how you''re applying the filter we can help you with a modified version.
Your question doesn''t make it clear what you''re doing exactly.
If you tell us how you''re building your SQL (or query) and how you''re applying the filter we can help you with a modified version.
Your question doesn''t make it clear what you''re doing exactly.Its a simple query that is based on one table that contains 15 columns of data. (14 columns are text based and 1 is a date). I am retrieving all 15 columns of data. I am currently filtering on two columns (Date and Name) and would like to add a third (Branch #). Branch numbers are from 00 - 99 and again stored as text
On the form (titled - Activity), the operator will enter a date in a text box (titled Date). In a separate text box the operator will enter the client name. (titled Name). In the query, date column criteria has the following -
forms!activity!date. The name column has the following - forms!activity!name.
When the operator launches the query from the form, it passes the date and name to the query and we get the results. I would like to add a filter for the branch number. We need to exclude certain branch numbers from the results to get at the data we are looking for.
I have tried the following with no luck. I created a new text box on the form (Titled Branch). In the query, branch column criteria has the following -
forms!activity!branch. I have tried passing in the following to filter on the branch number (keep in mind the branch number column is stored as text)
Not In ("59","99")
<> "59" and <> "99"
If either of the above filters are used from the form, we get 0 items returned.
I found that Access places single '' around the filters above and thats why I believe we are getting 0 records returned. If I use the filters above directly in the query criteria field we get the correct results. Is there another way to filter the branch number that will return results?
Just wondering... Why is a number being stored as text? You may want to try putting #59# around the numbers. And also might want to try putting a parameter for the numbers being calculated as text and not numbers.
Just a thought.
here is the sql for what the parameter would look like:
PARAMETERS forms!activity!branch Text ( 255 );
SELECT (your select clause)
FROM (etc..)
WHERE (etc...)
Its a simple query that is based on one table that contains 15 columns of data. (14 columns are text based and 1 is a date). I am retrieving all 15 columns of data. I am currently filtering on two columns (Date and Name) and would like to add a third (Branch #). Branch numbers are from 00 - 99 and again stored as text
On the form (titled - Activity), the operator will enter a date in a text box (titled Date). In a separate text box the operator will enter the client name. (titled Name). In the query, date column criteria has the following -
forms!activity!date. The name column has the following - forms!activity!name.
When the operator launches the query from the form, it passes the date and name to the query and we get the results. I would like to add a filter for the branch number. We need to exclude certain branch numbers from the results to get at the data we are looking for.
I have tried the following with no luck. I created a new text box on the form (Titled Branch). In the query, branch column criteria has the following -
forms!activity!branch. I have tried passing in the following to filter on the branch number (keep in mind the branch number column is stored as text)
Not In ("59","99")
<> "59" and <> "99"
If either of the above filters are used from the form, we get 0 items returned.
I found that Access places single '' around the filters above and thats why I believe we are getting 0 records returned. If I use the filters above directly in the query criteria field we get the correct results. Is there another way to filter the branch number that will return results?
这篇关于将信息从表单传递到查询作为条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
