pls check this sql statement

hi guys....

i m using following sql statement to search in two tables of database which r connected by the field 'numeroperation' ....

mysql = "SELECT recevoi.date, recevoi.description, recevoi.numeroperation, recevoi.societe, piece_indice.nompiece, piece_indice.numeropiece, piece_indice.indice,piece_indice.concerne,piece_indice.valablepour FROM recevoi, piece_indice "

strfiltre = "recevoi." & cmbsearchfiltre.Text

Set rs = cn.Execute(mysql & "where recevoi.societe = '" & cmbsearch.Text & "' and recevoi.numeroperation = piece_indice.numeroperation and strfiltre = '" & cmbfiltre.Text & "'")

here i m using variable 'strfiltre' as string which is creating a problem...

this statement is currently not working...pls tell me what is the problem and can i use the string variable as i have used it here...and if no, pls tell me anyother way of performing search using unknown criteria selected by the user.....

thanx.

Try concatenating the value of strfiltre into the query string.

Set rs = cn.Execute("... and " & strfiltre & " = '" & cmbfiltre.Text & "'")

Hi,

1.You can't refer to a field's name this way (by doing concantenation).
Not a correct statetement in this context, I rather meant doing something similar when dealing with recordsets' fields ---> rs.Fields("Field1").
2. For the rest your whole sql seems to be not really correct, this part:

"...and recevoi.numeroperation = piece_indice.numeroperation and strfiltre = '" & cmbfiltre.Text "

'strfiltre' is a variable so needs to be concantenated too (but again even if you do this it won't work, see 1.)

In my view you'll need to compose the sql on the fly (think of SELECT CASE or so) to append the last part the Where clause:

'very schematic"

....
Select Case cmbsearchfiltre.Text
Case "bla"
sSQL="recevoi.bla"
Case "blabla"
sSQL="recevoi.blabla"

End Select


Set rs = cn.Execute(mysql & "where recevoi.societe = ....... " & sSQL ...)





Regards,
Shurik.

thanx for the rplies

what 00100b suggested works perfectly fine....

thanx for that.

agree, it's a more elegant way of doing it.