random search

deepakaa

01-09-2004, 07:11 AM

my database is as shown below

student id.....name.............family name..........country........
....001...........X......................A.....................USA.... ....
....002...........Y......................B......................CANADA .....
....003...........z......................c......................Brazil .....
....
....

table 2 :

student id........subject............marks.....remarks...
....001............maths.................50.....needs to improve,fail in lab
....001...........chemistry..............60......fail in lab
....002............physics................80.....good in lab, good in theory
....003............english................75.....good
....003............french.................85.......very good

now i want to write a sql statement to perform search according to the random word given by the user in a text box.....the word given by the user can be anything like contry name-usa, or even it can be a single word from the remarks column like 'lab' or 'good'....and in result it has to show the corresponding data from both the tables

pls tell me how to write sql statement for this...

thanx.

candela

01-09-2004, 07:35 AM

u what a record that's a Join of the 2 table?
if it is so, u have to make SQLstring like
SELECT * FROM table1 inner join table2
ON table1.idstudent=table2.idstudent
WHERE instr(table1.country,TextSEARCh)>0 OR instr(table2.remarks,TextSEARCh)>0
... i think....

deepakaa

01-09-2004, 07:47 AM

thanx candela for the reply...here what is 'instr' and what is 'textSEARCh'
and does this mean that if i have 20 fields in the table i have to write like instr(table1.remarks,TextSEARCh)>0, instr(table2.country,TextSEARCh)>0, instr(table2.subject,TextSEARCh), instr(table2.name,TextSEARCh)....

candela

01-09-2004, 08:13 AM

i suggest u to build your own "SQL BUILDER", it won't be so difficult, otherwise yes u have to make 20 OR statment (not with ,).
INSTR is a function, look in your help 4 the sintax!
TextSEARCH is the text u wont ot search:
mySQL = "SELECT ......... "
' if u know the text:
mySQL = mySQL & "WHERE (instr(tableA.fieldx,'cansas')>0) OR ...."
' if u have the text in a varible
mySQL = mySQL & "WHERE (instr(tableA.fieldx,'" & mySearchVAR & "'')>0) OR ...."

deepakaa

01-09-2004, 08:17 AM

ok i got it what it is pls but the problem is that when there is any number in the remarks column it doesnt work...for example...

for id 005.....remarks is total 890...
and for id 009...remarks is second highest 90....

now if i search by writing '90' in column it doesnt display id 009 rather it displays id 005...but i dont want to display id 005 and only want to display 005

candela

01-09-2004, 08:26 AM

u have to chose how to store the data.
if the field type is text u won't find anydifferent if u serach
90
in string like 1234567890 or 90 or HALL 9000
so u can chose to store your data inside it with ' or with ;
so u have:
1° ;1234567890:
2° ;90;
3° ;HALL 9000:
and if u search ;90; u'll find only 2°
;)

candela

01-09-2004, 08:29 AM

i mean:
1° ;1234567890;
2° ;90;
3° ;HALL 9000;
and if u search ;90; u'll find only 2°

deepakaa

01-09-2004, 08:39 AM

ya i should find 2° but i m finding 1° and 3°
what could be the problem

candela

01-09-2004, 08:45 AM

write down your sql string

deepakaa

01-09-2004, 09:09 AM

Set adorecordset_recevoi = envoi_recevoi.Execute("SELECT recevoi.date, recevoi.description, recevoi.numeroperation, recevoi.societe, piece_indice.nompiece, piece_indice.numeropiece, piece_indice.indice FROM recevoi inner join piece_indice ON recevoi.numeroperation = piece_indice.numeroperation Where InStr(recevoi.societe, '" & Text1.Text & "') > 0 Or instr(piece_indice.numeropiece, '" & Text1.Text & "') > 0")

Dennis DVR

01-09-2004, 09:25 AM