从字符串中提取的ID无法用于连接绑定形式-“表达式...太复杂"； [英] ID Extracted from string not useable for connecting to bound form - "expression ... too complex"

问题描述

我在Access数据库中有一个指向Outlook Mailitem文件夹的链接表.这样做很方便，因为它可以不断进行自我更新，但是我无法添加额外的字段来将这些记录与父表相关联.

I have a linked table to a Outlook Mailitem folder in my Access Database. This is handy in that it keeps itself constantly updated, but I can't add an extra field to relate these records to a parent table.

我的解决方法是将自动生成/添加的ID字符串放入主题中，以便从那里开始工作.为了使表单按照我需要的方式工作，我正在尝试创建一个查询，该查询从链接表中获取我需要的字段，并添加一个带有提取的ID的计算字段，以便可以将其引用为与之相关的记录.表格.

My workaround was to put an automatically generated/added ID String into the Subject so I could work from there. In order to make my form work the way I need it to, I'm trying to create a query that takes the fields I need from the linked table and adds a calculated field with the extracted ID so it can be referenced for relating records in the form.

查询工作正常(我提取了所有记录及其ID)，但是当我尝试通过计算字段从该查询中过滤记录时，我得到:

The query works fine (I get all the records and their IDs extracted) but when I try to filter records from this query by the calculated field I get:

此表达式的键入错误，或者过于复杂而无法评估.例如，一个数字表达式可能包含太多复杂的元素.尝试通过将表达式的一部分分配给变量来简化表达式.

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

我尝试将计算出的字段分为三个字段，以使其更易于阅读，希望这样可以更轻松地评估Access，但是我仍然遇到相同的错误.我的基本查询当前为:

I tried separating the calculated field out into three fields so it's easier to read, hoping that would make it easier to evaluate for Access, but I still get the same error. My base query is currently:

SELECT InStr(Subject,"Support Project #CS")+19 AS StartID, 
       InStr(StartID,Subject," ") AS EndID, 
       Int(Mid(Subject,StartID,EndID-StartID)) AS ID, 
       ProjectEmails.Subject, 
       ProjectEmails.[From], 
       ProjectEmails.To, 
       ProjectEmails.Received, 
       ProjectEmails.Contents
FROM ProjectEmails
WHERE (((ProjectEmails.[Subject]) Like "*Support Project [#]CS*"));

我尝试将子表单绑定到qryProjectEmailWithID.ID = SupportProject.ID上的此查询，其中主表单绑定到SupportProject，但出现上述错误.我尝试构建一个查询，该查询从该查询中选择ID =给定参数的所有记录，但仍然出现相同的错误.

I've tried to bind a subform to this query on qryProjectEmailWithID.ID = SupportProject.ID where the main form is bound to SupportProject, and I get the above error. I tried building a query that selects all records from that query where the ID = a given parameter and I still get the same error.

添加支持项目ID的工作查询如下所示:

The working query that adds Support Project IDs would look like:

+----+--------------------------------------+----------------------+----------------------+------------+----------------------------------+
| ID |               Subject                |          To          |         From         |  Received  |             Contents             |
+----+--------------------------------------+----------------------+----------------------+------------+----------------------------------+
|  1 | RE: Support Project #CS1 ID Extra... | [e-mail]     | [e-mail] | 2019-03-11 | Trying to work out how to add... |
|  1 | RE: Support Project #CS1 ID Extra... | [e-mail] | [e-mail]     | 2019-03-11 | Thanks for your question. The... |
|  1 | RE: Support Project #CS1 ID Extra... | [e-mail] | [e-mail]     | 2019-03-11 | You should use a different me... |
|  2 | RE: Support Project #CS2 IT issue... | [e-mail]   | [e-mail]  | 2019-02-21 | I really need some help with ... |
|  2 | RE: Support Project #CS2 IT issue... | [e-mail]  | [e-mail]   | 2019-02-21 | Thanks for your question. The... |
|  2 | RE: Support Project #CS2 IT issue... | [e-mail]  | [e-mail]   | 2019-02-21 | Have you tried turning it off... |
|  3 | RE: Support Project #CS3 email br... | [e-mail]   | [e-mail]  | 2019-02-12 | my email server is malfunccti... |
|  3 | RE: Support Project #CS3 email br... | [e-mail]  | [e-mail]   | 2019-02-12 | Thanks for your question. The... |
|  3 | RE: Support Project #CS3 email br... | [e-mail]  | [e-mail]   | 2019-02-13 | I've just re-started the nece... |
+----+--------------------------------------+----------------------+----------------------+------------+----------------------------------+

有问题的视图将填充一个数据表，该数据表看起来与ID与当前SupportProject记录的ID匹配的项目相同，并在选择新记录时进行更新.单独的文本框应显示在那个网格中选择的记录的全部内容，例如:

The view in question would populate a datasheet that looks the same with just the items whos ID matches the ID of the current SupportProject record, updating when a new record is selected. A separate text box should show the full content of whichever record is selected in that grid, like this:

您是否尝试过将其关闭然后再次打开?

Have you tried turning it off and on again?

发件人:[e-mail]

From: [e-mail]

在:2019年2月21日

On: 21/02/2019

感谢您的提问.此问题已分配给支持项目#CS2，支持人员将尽快与您联系以帮助您.由于它被认为是中等优先级，因此您应该期待每日更新.

Thanks for your question. The matter has been assigned to Support Project #CS2, and a support staff member will be in touch shortly to help you out. As it is considered of medium priority, you should expect daily updates.

谢谢

支持

来自:某人@公司

在:2019年2月21日

On: 21/02/2019

我的计算机确实需要一些帮助.似乎真的很慢，我无法高效地完成工作.

I really need some help with my computer. It seems really slow and I can't do my work efficiently.

当我尝试使用计算出的数字与SupportProject表的PK相关时，这些事情都不会发生...

Neither of these things happens as when I try to use the calculated number to relate to the PK of the SupportProject table...

我不知道这是否是问题的一部分，但是无论我使用Int(Mid(Subject...还是Val(Mid(Subject...，我显然仍然会获得Double，其中ID字段(作为自动增量ID)为Long.我无法解决如何强制它返回Long的问题，所以我无法测试这是否是问题所在.

I don't know if this is a part of the problem, but whether I use Int(Mid(Subject... or Val(Mid(Subject... I still apparently get a Double, where the ID field (as an autoincrement ID) is a Long. I can't work out how to force it to return a Long, so I can't test whether that's the problem.

推荐答案

那是发布的SQL生成的输出?我确实想要原始数据，但足够接近.如果要求提取...CS之后的数字，请在查询中计算并保存查询:
Val(Mid([Subject],InStr([Subject],"CS")+2))

So that is output resulting from posted SQL? I really wanted raw data but close enough. If requirement is to extract number after ...CS, calculate in query and save query:
Val(Mid([Subject],InStr([Subject],"CS")+2))

然后构建另一个查询以将第一个查询连接到表.

Then build another query to join first query to table.

SELECT qryProjectEmailWithID.*, SupportProject.tst
FROM qryProjectEmailWithID
INNER JOIN SupportProject ON qryProjectEmailWithID.ID = SupportProject.ID;

过滤条件可以应用于任何一个ID字段.

Filter criteria can be applied to either ID field.

子窗体可以在主窗体上显示与SupportProject记录同步的相关子记录.

A subform can display the related child records synchronized with SupportProject records on main form.

我先用您的数据然后通过指向我的收件箱的链接测试了ID calc.查询联接没有问题.

I tested the ID calc with your data and then with a link to my Inbox. No issue with query join.

这篇关于从字符串中提取的ID无法用于连接绑定形式-“表达式...太复杂"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！