Query not working with < or >

[laman]

I am using the following code to run a query from a form with user input from a textbox for criteria.

Dim dbMyDB As Database
Dim qdMyQuery As QueryDef
Dim rsMyRS As Recordset
Dim strMySQL As String


strMySQL = " Select * from master WHERE [wl] = " & Text2 & ";"

Set dbMyDB = CurrentDb
Set qdMyQuery = dbMyDB.CreateQueryDef("MyQuery", strMySQL)

DoCmd.OpenQuery qdMyQuery.Name

It works fine if I enter only a number but does not work with < or >.

Any ideas,
Thanks

 

[JimHorton]

laman,
Do you mean it fails if you enter the 'OR' in the texbox? ie user enters: &quot;5 or 6&quot; ?
You can't do this in this way, if that's the case. You'd need to have the texbox contain the entire criteria, in which case the user would have to know field names, ie he'd have to type in the textbox &quot;5 or WI = 6&quot;
You could have 2 textboxes Text2 and Text3, and change the sql:

strMySQL = &quot; Select * from master WHERE [wl] = &quot; & Text2 & _
Iif(nz(text3,&quot;&quot = &quot;&quot;,&quot;&quot;,&quot; OR WI = &quot; & Text3)

If WI is text, you'd need to to adjust by adding quotes appropriately.
--Jim

 

[JohnLowell]

You could also use IN.

i.e. &quot; Select * from master WHERE [wl] In (&quot; & Text2 & &quot&quot;
Text2 would need to be separated by commas (5, 6, 7)

 

[laman]

I'm sorry, what I meant was if I type in 20 it works but does not work if I type in >20. The error is missing operator in query expression '[wl]=>20'.

 

[JimHorton]

laman,
Again, you can't do that, since the '>' is considered part of the value, it's not seen as an operator. So here again, you'd need to have users who are versed in query syntax--which most arent, which is why I don't recommend having a textbox that expects users to know fieldnames and query syntax, and even typing comma delimited values for the IN predicate is too complex for some users (!), especially when you consider that you may need Quotes, ie IN('A', 'B', 'C') for example, you'd get users typing in A,B,C in the textbox.

You can make a combobox with &quot;=&quot;, &quot;>=&quot;, &quot;LIKE&quot;, etc, and then build your sql with the chosen operator, further, you could add a combobox with &quot;AND&quot; ,&quot;OR&quot; as values, and put it between Text2 and Text3, and go from there.

--Jim