Textbox value not returning query results

[RosieMP]

I have a textbox on a form for users to use to enter one or a series of contracts, separated by commas. (txtEntry)

I have another invisible textbox I use to hold the value of the first textbox in a query-friendly format, so the first box might hold "1,2" and the second holds "1 or 2." (I just do a string manipulation to get there) So that's txtQueryValue.

I know that the variable "1 or 2" is successfully passed to the query (which I'm manipulating in design view, not code) because I added txtQueryValue in as an expression and it will show up in the query results.

I know that typing "1 or 2" directly into the query into the query will return the results I want, because I tried it and it did.

However, when I pass txtqueryvalue into the 'contracts' field in the query, I don't get any results.

This is kind of driving me nuts. Can anyone explain what might be the problem, or what might be a better way to accomplish what I'm trying to do, which is just allow the user to pass multiple contracts to the query?

Thanks very much

 

[Remou]

You are passing text "1 or 2", which won't work. IN may suit, but you will have to build the SQL string programmatically.

 

[RosieMP]

Thanks....

I wound up creating a temporary table to hold the values the user keys, then calling a query tied to that temp table and programmatically deleting the contents of the table once the report gets called up. Next time I'll build the SQL for the report programmatically -- I just wound up going with the solution I already knew this time.

I'm curious, though, why won't it work to pass the text "1 or 2" but if I key it in to the form designer it does work?

Is it that the form designer reformats that value? Is there really no way to do this without coding it?

 

[Remou]

When you key in, you get boolean Or, when you use a textbox, you get the word "or", which is a different thing altogether. There is very little coding involved, for example:

'txtText, eg, 1,2,9
'Field1 is numeric

strSQL="SELECT ID, Field1 From tblTable " _
& "WHERE Field1 IN ( " & Me.txtText & ")"

'Check and set-up output query
'May be version dependant
If DLookup("Name", "MSysObjects", "Name= 'tmpOutput'") <> "" Then
    Set qdf = CurrentDb.QueryDefs("tmpOutput")
    qdf.SQL = strSQL
Else
    Set qdf = CurrentDb.CreateQueryDef("tmpOutput", strSQL)
End If

 

[RosieMP]

Cool--thanks!