Question with an eval() function

[schreibman]

Hello,
I have a form, and it has a text box which if the users enters a number, or numbers delimited by commas, I'd like to pass along to a query.
I am pulling out my hair.
I'm sure I am BOTH missing somethign easy like a missing " or ' and I am sure there is an easier way to do this!
In my form, NumberList is a text box and I enter in for example: 1,2

But, I am missing something!

Field Expr1: IIf([Forms]![Main Form]![NumberList] Is Null, True,Eval( "[PlayerNumber] in (" & [Forms]![Main Form]![NumberList] &")" ))

Criteria True

 

[Brianwarnock]

Ah, but what you actually have in you text box is a string "1,2,3" not a numeric list , I believe that you need to write code to handle this. Pat Hartman posted some many years ago but I have no idea how to find it.

Brian

 

[apr pillai]

It looks like you are using Eval() function to concatenate the values, which is not necessary. The IN Clause is normally used in the criteria row of a Column to pick the matching either of the two values (1,2) from the recordset.

If you want simply pass the value entered on the Form to a column of the Query for display purposes (it will be repeated for all output records) then don't use the IN Clause in the field expression.

Field Expr1: IIf([Forms]![Main Form]![NumberList] Is Null, True,[Forms]![Main Form]![NumberList])

If you are using it as Criteria to pick the matching records then use the expression in the WHERE Clause:

SELECT Field1, Field2, Field3
FROM Table1
WHERE (Table1.[Field3] IN (IIf([Forms]![Main Form]![NumberList] Is Null, True,[Forms]![Main Form]![NumberList])));

Check the following for a sample usage of Eval() Function:Custom Calculator and Eval() Function