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.
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,
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... :(
Copyright © 1999-2008 by Shamil Salakhetdinov. All rights reserved.