Query input unintentionally parsed.

[Arrows]

I have a table in a database that contains 100+ numbered, multiple-choice questions and a user query that allows the user to type in one or several question numbers. The intended result of the query is a report listing each selected question and their associated answers. It is to allow the instructor to tailor a mini-quiz from the master database.

However, when the user enters any two digit number (eg 13) the result generated is the intended number plus the individual components (1, 3, 13 instead of just 13).

I am using the query: Expr1: InStr(1,[Enter question numbers separated by commas],[Tbl_Questions.Key])

I have a solution but it requires two queries. One query for questions 1-9 and another for questions 10-99 but this is not really acceptable to me or my colleagues since both values <10 and >10 are expected to be entered into a single query.

Is this doable or am I asking the impossible?

 

[mdmilner]

Yes it is.
What you do is build a custom function that handles both conditions.
As an explanation, think of all the built-in functions in a product like Excel. Sum, MAX, MIN, etc. They all do a complex task..well, you get to define that task.

In your query, you'll end up putting something like:

functionname([fieldname1])

Your code will probably look something like:

Function functioname(ByVal aname As String) As String

If (test variable aname) = condition Then
  'functioname = some calculation you do
else
   'functioname = other calculation
End if

End Function

Mike