join in select statement

[plusplus]

I have the following select statement which works, I need help to adapt it

adobau.RecordSource = "SELECT * FROM Liegenschaften INNER JOIN tblbau ON Liegenschaften.Rimo_Liegnr = tblbau.Liegenschaft"

my adobau.recordset loops through Liegenschaften and gives me all the info of Liegenschaften that has match in tblbau, but I want some info from tblbau also, how would I do this?

[debasisdas]

there is no problem with the query . You might need to change the join condition . Kindly post both of your tables structure for more details.

[plusplus]

tbl 1 has fields id, name,address
tbl2 has fields id, date
tbl2.id refers to same id of tbl1
I want all records from tbl2 print the name address and date.
name and address are in tbl1 and date in tbl2
Can this be done in one select statement?

[yello]

Try this query.

adobau.RecordSource = "SELECT Liegenschaften.name,Liegenschaften.address,tblbau.date FROM Liegenschaften INNER JOIN tblbau ON Liegenschaften.Rimo_Liegnr = tblbau.Liegenschaft"

[plusplus]

My problem is not the recordsource, it gives me all the records I need.
My problem is the recordset, it points only to one table and I need info from secondtable also

[culebrin]

I think you should give a try the yello's suggestion, and to call certain column use: adobau("columnname"), columnname could be any of the columns used in the query, not exclusively of a certain table, the recordset does not know anything about the tables used in your query, it only can see the columns used in the query, all of them:

p.e:
adobau.RecordSource = "SELECT Liegenschaften.name,Liegenschaften.address,tblbau.date FROM Liegenschaften INNER JOIN tblbau ON Liegenschaften.Rimo_Liegnr = tblbau.Liegenschaft"

and the call:

textbox1.text = adobau("name")
textbox2.text = adobau("address")
textbox3.text = adobau("date")

and so on no matter which table you used in the query. That's why you should name with different names, similar columns in both tables.

Cheers,

Omar

[plusplus]

ok, I guess that was my problem, I have in both tables a column with same name and when I try to get that one it gives me an error. I thought it's because the recordset doesn't point to that table, now I see that it's because it doesn't know which column, which table.
I can't change the names of the columns, because I'm using an existing database that's being used for hundreds of programs already. I got it to work with two ados and two select statement, but I'd still like to do it in one. Do you have any suggestions?

[yello]

If you have same column name in two tables you must refer the column by [Table Name].[Column Name]

[plusplus]

It works thank you all for your help
this was the syntax that worked
ado1.recordset ("tblname.columnname")