pls check this sql statement

deepakaa
01-12-2004, 08:32 AM
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.

00100b
01-12-2004, 08:42 AM
Try concatenating the value of strfiltre into the query string.

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

Shurik12
01-12-2004, 08:48 AM
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.

deepakaa
01-12-2004, 08:58 AM
thanx for the rplies

what 00100b suggested works perfectly fine....

thanx for that.

Shurik12
01-12-2004, 09:07 AM
agree, it's a more elegant way of doing it.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum