Sql In

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

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

Code (markup):

But its hshowing the following error

Microsoft JET Database Engine- Error '80040e14'

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

Code (markup):

 

maybe this could work
strSQL = "select * from tblcontest where bar_mob_no='" & sSQL &"' or bar_alt_mob='" & sSQL &"' "

if the fields are integers, you'd beter use it this way
strSQL = "select * from tblcontest where bar_mob_no=" & sSQL &" or bar_alt_mob=" & sSQL &" "

also you may wanna try using the LIKE in the SQL statement

 

A mobile phone number should be stored as text to maintain any zeros at the start and any spacing.

Getting this to work requires that the numbers are always stored the same way - eg with spaces or without spaces - otherwise it'll be difficult.

Assuming sSQL is the mobile number, I think this is the code you need.

strSQL = "select * from tblcontest where bar_mob_no = '" & sSQL &"' or bar_alt_mob = '" & sSQL &"' "

 

why your sSQL is '44444444'
the sSQL should be 44444444
or chang IN(' to IN( and ') to )
by cut the ' out from your statement

both all are another way ok.

 

Honeydesign - a telephone number is not numerical data, because it may have a zero at the front and spaces in it. It is a text string.

eg
05555 555555

 

Grave you right ,the Tel No. shoule be text not number as you point.

but in the error is say
*******************
Microsoft JET Database Engine- Error '80040e14'

Syntax error (missing operator) in query expression 'bar_mob_no IN(''44444444'') or bar_alt_mob in(''44444444'')'.
***********************
you will see the 2 ' [chr(39)] in the SQL statment , it not 1 " [chr(34)]
in the front and the end of 44444444

so the sSQL variable is content '44444444' (len() is 10) not 44444444 (len() is 8)

in the best way should cut the chr(39) out from the sSQL variable.

The last, in the ASP code should have A SPACE in the end of "&" too.

Nice to see u