Expression Too Complex Access 2007，如何简化此IIF序列

Question

我正在运行下面的复杂语句，以在"SODist"列中提供一个值，该值随着"SalesOrderNo"字段中每个连续匹配值的增加而增加。我使用"ID"作为自动编号来模拟"行号"函数，但我需要将其扩展到超过13行。有没有更好的方法可以使用VBA函数来实现我想要的结果？

IIf(DLookUp("[SalesOrderNo]","[Peachtree-Import-Dist]","[ID]=" & [ID]-1)=[SalesOrderNo],IIf(DLookUp("[SalesOrderNo]","[Peachtree-Import-Dist]","[ID]=" & [ID]-9)=[SalesOrderNo],10,IIf(DLookUp("[SalesOrderNo]","[Peachtree-Import-Dist]","[ID]=" & [ID]-8)=[SalesOrderNo],9,IIf(DLookUp("[SalesOrderNo]","[Peachtree-Import-Dist]","[ID]=" & [ID]-7)=[SalesOrderNo],8,IIf(DLookUp("[SalesOrderNo]","[Peachtree-Import-Dist]","[ID]=" & [ID]-6)=[SalesOrderNo],7,IIf(DLookUp("[SalesOrderNo]","[Peachtree-Import-Dist]","[ID]=" & [ID]-5)=[SalesOrderNo],6,IIf(DLookUp("[SalesOrderNo]","[Peachtree-Import-Dist]","[ID]=" & [ID]-4)=[SalesOrderNo],5,IIf(DLookUp("[SalesOrderNo]","[Peachtree-Import-Dist]","[ID]=" & [ID]-3)=[SalesOrderNo],4,IIf(DLookUp("[SalesOrderNo]","[Peachtree-Import-Dist]","[ID]=" & [ID]-2)=[SalesOrderNo],3,IIf(DLookUp("[SalesOrderNo]","[Peachtree-Import-Dist]","[ID]=" & [ID]-1)=[SalesOrderNo],2,1))))))))),1) AS SODist

Answer 1

以下是我将如何处理它：

我会在Access中创建一个名为[CountPreviousSalesOrderLines]的已保存查询

PARAMETERS prmID Long, prmSalesOrderNo Text(255);
SELECT COUNT(*) AS n
FROM [Peachtree-Import-Dist]
WHERE SalesOrderNo=[prmSalesOrderNo] AND ID<=[prmID];

然后我会在数据库中添加一个标准模块，其中包括以下功能

Option Compare Database
Option Explicit
Public Function getSODist(ID As Long, SalesOrderNo As String) As Long
    Dim cdb As DAO.Database, qdf As DAO.QueryDef, rst As DAO.Recordset
    Set cdb = CurrentDb
    Set qdf = cdb.QueryDefs("CountPreviousSalesOrderLines")
    qdf!prmID = ID
    qdf!prmSalesOrderNo = SalesOrderNo
    Set rst = qdf.OpenRecordset(dbOpenSnapshot)
    getSODist = rst!n
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set cdb = Nothing
End Function

对于[Peachtree Import Dist]中的样本数据

ID  SalesOrderNo
--  ------------
 1  001         
 2  001         
 3  001         
 4  001         
 5  002         
 6  003         
 7  003         
 8  003         
 9  002         

查询

SELECT ID, SalesOrderNo, getSODist(ID,SalesOrderNo) AS SODist
FROM [Peachtree-Import-Dist]

返回

ID  SalesOrderNo  SODist
--  ------------  ------
 1  001                1
 2  001                2
 3  001                3
 4  001                4
 5  002                1
 6  003                1
 7  003                2
 8  003                3
 9  002                2

为了获得最佳性能，请确保[SalesOrderNo]字段已编入索引：Yes (Duplicates OK)。

Answer 2

看起来您正在尝试获取每个[SalesOrderNo]的运行计数。是这样吗？

在新模块中，粘贴以下代码：

Function RunningCount(WhatToCount As String) As Integer
Static CountSoFar As Long, var  As String
If var <> WhatToCount Then       '  Is WhatToCount different from the one before?
        CountSoFar = 0
        var = WhatToCount        ' save the value of this one for comparison with the next
End If
    CountSoFar = CountSoFar + 1  ' increment the variable
    RunningCount = CountSoFar    ' return the result to the query
End Function

在查询中，您使用如下函数：

SODist:运行计数（[SalesOrderNo]）

确保查询在[SalesOrderNo]上排序