Expression Too Complex Access 2007, how can I simplify this IIF sequence?

Question

I am running the below complex statement to provide an value in Column "SODist" that increments for each successive matching value in the "SalesOrderNo" field. I use an "ID" as auto-number to simulate a "row number" function but I need to scale this to beyond 13 lines. Is there a better way perhaps in using a VBA function to achieve my desired results?

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

Here is how I would approach it:

I'd create a saved query in Access named [CountPreviousSalesOrderLines]

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

Then I would add a standard Module to the database that included the following function

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

For the sample data in [Peachtree-Import-Dist]

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

the query

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

returns

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

For best performance, make sure that the [SalesOrderNo] field is indexed: Yes (Duplicates OK).