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

I loaded the two queries and the module from Gord's suggestion and am getting an undefined function on "GetSODist", any advice? I loaded these in from a copy/paste. — Mike Dowdal, Nov 26 '13 at 17:41
tbur, the module you provided almost works, except it increments a 3-line order to 1,2,1. See the screen below: http://screencast.com/t/zJ2CEiQSA — Mike Dowdal, Nov 26 '13 at 17:47
Try copying and pasting again from my revised answer. (I tweaked one of the function parameters. Not that it would affect your "undefined function" issue, but it would have caused problems with large tables.) If you got the other module to work then I don't understand why mine doesn't. — Gord Thompson, Nov 26 '13 at 18:05
I am confused too. The only difference I can see in the two modules is that tbur's uses static and you declare variables. Am I missing something? — Mike Dowdal, Nov 26 '13 at 18:13
You can download a working example [here](http://wikisend.com/download/949972/SODist.zip), complete with a test table containing just over a million rows. Let me know if it works for you. — Gord Thompson, Nov 26 '13 at 18:46
Gord, thank you! For some reason, copying the module out of your .accdb into mine made it function! This is a lifesaver! — Mike Dowdal, Nov 26 '13 at 18:56

Gord Thompson · Accepted Answer · 2013-11-26T18:01:50.403

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).

score 0 · Answer 2 · answered Nov 26 '13 at 17:43

It looks like you are trying to get a running count of each [SalesOrderNo]. is that right?

In a new module, paste the following code:

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

In the query, you use the function like this:

SODist:RunningCount([SalesOrderNo])

Make sure the query sorts on [SalesOrderNo]

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

2 Answers2