Jump to content

Sql Join With Search Option


Guest Brice

Recommended Posts

GreetingsI have two tables in one msaccess database. It is a one-to-many relationship.Table A is the "Master" table, while table B will hold records relating to a record in table A.I am working in VB6.I have a form that displays all records from table B, as well as able to navigate all the records.I also have a search option for two specific fields in table B. These options as chosen by the use of radio buttons.My Search code is as follows: RS.Close RS.ActiveConnection = CN If optDebtor.Value = True Then RS.Source = "Select * from tableB full join tableA to tableB.refno=tableA.refno where tableB.name like '%" & txtSearch.Text & "%'order by name" ElseIf optCompany.Value = True Then RS.Source = "Select * from tableB full join tableA to tableB.refno=tableA.refno where tableB.co like'%" & txtSearch.Text & "%'order by co" End If RS.CursorType = adOpenStatic RS.LockType = adLockOptimistic RS.Openerror messageMethod 'Open' of object '_Recordset' failedThe object of the above sql queries is to display all the information from tableB and only the fields (tableA.name and table A.co) in a label. this is to see which "name and co" belong to the information displayed from tableB.I have tried Inner join as well as Outer join as well as Right join as well as Left join, but without any success.I would sincerely appreciate any assistance.Thanking You in AdvanceBrice

Link to comment
Share on other sites

It's probably the SELECT * that is the issue, if the tables have columns with the same name it won't work. You would need to list which columns you want to get and give them aliases if they have the same names. If you only need to select the name and co from the one table, it's probably best not to use * anyway.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...