SQL IN

cancer10 · October 30th, 2006, 12:22 AM

Does anyone know the exact query for IN in the following senario...

I want to search for 2 columns for 1 mobile number (mobile, alternate mobile)

I am using the following query

Code:

strSQL = "select * from tblcontest where bar_mob_no IN('" & sSQL &"') or bar_alt_mob in('" & sSQL &"')"

But its hshowing the following error

Code:

Microsoft JET Database Engine- Error '80040e14'

Syntax error (missing operator) in query expression 'bar_mob_no IN(''44444444'') or bar_alt_mob in(''44444444'')'.

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting

cyberddindia · October 30th, 2006, 07:31 AM

The code has been tested and it is working.
The query is correct.
There is no error in your code while using IN.

woodyz · October 30th, 2006, 02:21 PM

You do have a problem. Your sSQL variable appears to contain a value that is enclosed in single quotes which results in an extra set of enclosing single quotes in your query.
You must only have one set of single quotes in your generated sql string.

The result from the error message:

Code:

bar_mob_no IN(''44444444'') or bar_alt_mob in(''44444444'')

What you want generated in your strSql:

Code:

bar_mob_no IN('44444444') or bar_alt_mob in('44444444')

Of course, that is assuming that the data type for bar_mob_no is some kind of text and not numeric. If you are using a numeric data type you don't want the single quotes at all.

Woody Z http://www.learntoprogramnow.com