这篇答案是关于查询中出现“定义的字段太多”错误的另一种情况,以及我采取的措施来消除它。
我有一个查询一直报错。压缩数据库并没有解决问题。当我将查询拆分成三个查询,并在每个查询中减少了一些字段后,其中两个较小的查询可以正常工作,但仍有一个查询会报错。因此,问题似乎与字段数量无关,而与查询编码有关。
查询中有两个类似的字段被编码为子查询:
(SELECT first([Date]) FROM [C:<path>\sources.accdb].[items - subjects] WHERE [Subject name] = SubjName and Assessment like "*Buy*") AS FirstBuyRecDate
并且
(SELECT first([Date]) FROM [C:<path>\sources.accdb].[items - subjects] WHERE [Subject name] = SubjName and Assessment like "*Sell*") AS FirstSellRecDate
当查询中添加第二个字段时,错误首次出现。查询可以处理任何一个字段的存在,但不能同时存在两个字段。如果将任一字段中的子查询替换为随机日期常量(例如#2018 12 31#
),则查询也可以正常工作。因此,问题似乎与这些子查询有关。
我通过使用我之前为其他目的开发的UDF将这些子查询更改为函数调用来消除错误:
Public Function vFtnInQry(sField As String, sSQL As String) As Variant
' Return the value of the field with the name <sField> in the select query <sSQL>.
' The purpose of this is to allow inserting a variable value into the SQL of a query.
Dim oRecSet As DAO.Recordset, nCountRecords As Long, vRetVal As Variant
Set oRecSet = CurrentDb().OpenRecordset(sSQL)
nCountRecords = oRecSet.RecordCount
If (nCountRecords = 0) Then
vRetVal = Null
Else
vRetVal = oRecSet.Fields(sField).Value
End If
vFtnInQry = vRetVal
End Function
所以字段定义已更改为:
vFtnInQry("BuyRecDate", "SELECT first([Date]) as BuyRecDate FROM [C:<path>\sources.accdb].[items - subjects] WHERE [Subject name] = """ & [SubjName] & """ and Assessment like ""*Buy*""")) AS FirstBuyRecDate
并且
vFtnInQry("SellRecDate", "SELECT first([Date]) as SellRecDate FROM [C:<path>\sources.accdb].[items - subjects] WHERE [Subject name] = """ & [SubjName] & """ and Assessment like ""*Sell*""")) AS FirstSellRecDate
我不知道为什么这样解决了问题。我在这里发布它,以防其他人遇到“定义的字段太多”的神秘查询错误,也许可以通过这种方式解决。另一方面,如果有人阅读此内容并发现那些子查询导致了该错误的原因,请在评论或另一个答案中提出。