recordset,sql not sure where my problem is

[plusplus]

Here is my code, it works when I leave away the line in bold, I can't figure out what's wrong with my line in bold, I've been checking my database so many times already, can you help me

With gridMieter
.Cols = 5
.TextMatrix(0, 0) = "Frist_id"
.TextMatrix(0, 1) = "Bau_id"
.TextMatrix(0, 2) = "Liegenschaft"
.TextMatrix(0, 3) = "Mieter"
.TextMatrix(0, 4) = "Kündigungsfrist"
'.COLWIDTH(0) = 0
'.COLWIDTH(1) = 0

adoMieter.RecordSource = "SELECT * FROM tblfrist INNER JOIN (tblMieter INNER JOIN Liegenschaften ON Liegenschaften.Rimo_Liegnr = tblMieter.Rimo_Liegnr) ON tblMieter.frist_id = tblfrist.frist_id"
adoMieter.Refresh

Do While Not adoMieter.Recordset.EOF
.Row = .Rows - 1
.TextMatrix(.Row, 0) = adoMieter.Recordset("tblfrist.Frist_id")
.TextMatrix(.Row, 1) = adoMieter.Recordset("tblfrist.Bau_id")
.TextMatrix(.Row, 2) = adoMieter.Recordset("Liegenschaften.Name")
.TextMatrix(.Row, 3) = adoMieter.Recordset("tblMieter.Name")
.TextMatrix(.Row, 4) = adoMieter.Recordset("Kündigungsfrist")
.Rows = .Rows + 1
adoMieter.Recordset.MoveNext

Loop

End With

[QVeen72]

Hi,

May be the field tblfrist.Bau_id is Null..

Just Concatenate it with a Blank String :

.TextMatrix(.Row, 1) = adoMieter.Recordset("tblfrist.Bau_id")  & ""

Regards
Veena

[plusplus]

I'm getting runtime error 3265 -id doesn't seem to find the field bau_id,I don't understand why

[QVeen72]

Hi,

First check if tblfrist table has got bau_id field.
and if it has got and if bau_id is not a common field in bot the used tables, then, just give

.TextMatrix(.Row, 1) = adoMieter.Recordset("Bau_id") & ""

Regards
Veena

[plusplus]

Bau_id exists only in one of the tables(thanks for pointing that out), so I changed tblfrist.bau_id to just bau_id and now it works. I'm still not sure why tblfrist.bau_id should cause problems

[QVeen72]

Hi,

If Fields are repeated, then Return Fields in Recordsets are prefixed with Tablename.FieldName ,
or else, only fieldnames are returned unless explicitly mentioned...

REgards
Veena