Mystery Code - Why does this Function work ?

Question

I have no idea why this code produces results, but it does: can anyone explain in plain English how it gets the proper result data? The purpose of this code is to allow an Input Box to simulate an SQL IN() statement. The user enters a series of order numbers separated by commas. No quotes, no parens, no forms. It's clear that the code parses the input string to get the individual (text) order numbers. That part I understand. What is a mystery is how the order numbers are used by Access in querying the table. How can an SQL WHERE statement equalling TRUE magically turn into a list of order numbers? What is Access doing behind the scenes? Can anyone figure this one out?

Easy to set up a demo: Use a simple table with a text field named ORDER_NBR. In the Query Properties list type in a text parameter: Enter ORDER List: . In the Query use this criteria: Where InParam([ORDER_NBR],[Enter ORDER List:])=True. Copy the following code into a module, then run the query:

Function InParam(Fld, Param)
     Dim stToken As String
     ' next 2 lines are optional
     Fld = UCase(Fld)
     Param = UCase(Param)
     If IsNull(Fld) Then Fld = " "
     Do While (Len(Param) > 0)
         stToken = GetToken(Param, ",")
         If stToken = LTrim$(RTrim$(Fld)) Then
             InParam = -1
             Exit Function
         Else
             InParam = 0
         End If
     Loop
     End Function
 
Function GetToken(stIn, stDelim)
     Dim iDelim As Integer, stToken As String
     iDelim = InStr(1, stIn, stDelim)
     If (iDelim <> 0) Then
         stToken = LTrim$(RTrim$(Mid$(stIn, 1, iDelim - 1)))
         stIn = Mid$(stIn, iDelim + 1)
     Else
         stToken = LTrim$(RTrim$(Mid$(stIn, 1)))
         stIn = ""
     End If
     GetToken = stToken
End Function

I believe this code came from the Access2 sample code database that ships with Access.

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: Microsoft Access Database Discussion List <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Mystery Code - Why does this Function work?
Date: 15 April 1998 12:21

Larry,

It works because it is called every time when the next row is processed by MS Access. And it is slow for large tables. This function :

Function InParam1(Fld, Param)
    On Error Resume Next
    InParam1 = Eval(Fld & " in (" & Param & ")")
End Function

is shorter producing the same results but you have to use comma as paramlist values delimiters. Or just place the following expression directly in your SQL:

WHERE (Eval([Fld] & " in (" & [Enter ORDER List:] & ")")=True)

Hope this explains your mystery code,
Shamil

P.S. Pity that MS Access does not allow to use "paramlist" parameters with saved querydefs and we have to use the proxies as above... :(