在报告计算字段中查询不相关的表(Query an Unrelated Table in Report

在报告计算字段中查询不相关的表(Query an Unrelated Table in Report- Calculated Field)

我正在创建一个报告，其中我想从不相关的表中获取值

SELECT Letter来自AnotherTable WHERE ThisField> = Tablex.Low AND ThisField <= Tablex.High （这会从等级表中得到一封信）

查询在计算字段中使用时单独工作，但不在报告中。 我只得到#Name？

该报告基于CrossTab，因此我无法在其中使用子查询

访问2010

I'm creating a report in which I'd like to get a value from an unrelated table

SELECT Letter FROM AnotherTable WHERE ThisField>=Tablex.Low AND ThisField<=Tablex.High (This gets a letter from a grades table)

The query works separately but not in the report when used in a calculated field. I only get #Name?

The report is based on a CrossTab, so I cannot use a subquery in it

Access 2010

最满意答案

SQL不能用作文本​​框的ControlSource。

尝试使用DLookup ：

=DLookup("[Letter]","[AnotherTable]","[ThisField] Between " & TableX!Low & " And " & TableX!High & "")

编辑 ：

尝试使用：

=DLookUp("[Letter]","[Grades]","[AVG] Between " & [ArtsLow] & " And " & [ArtsHigh] & "")

或者，如果AVG是文本：

=DLookUp("[Letter]","[Grades]","[AVG] Between '" & [ArtsLow] & "' And '" & [ArtsHigh] & "'")

EDIT2：

这是相反的：

=DLookUp("[Letter]","[Grades]","" & Nz([AVG],-1) & " Between [ArtsLow] And [ArtsHigh]")

SQL cannot be used as ControlSource for a textbox.

Try using DLookup:

=DLookup("[Letter]","[AnotherTable]","[ThisField] Between " & TableX!Low & " And " & TableX!High & "")

Edit:

Try with either:

=DLookUp("[Letter]","[Grades]","[AVG] Between " & [ArtsLow] & " And " & [ArtsHigh] & "")

or, if AVG is text:

=DLookUp("[Letter]","[Grades]","[AVG] Between '" & [ArtsLow] & "' And '" & [ArtsHigh] & "'")

Edit2:

It's the other way round:

=DLookUp("[Letter]","[Grades]","" & Nz([AVG],-1) & " Between [ArtsLow] And [ArtsHigh]")