SQL Joins 1

[timb94]

If this question should be posted in the VBA Coding forum, please let me know and I'll repost it there.

I am trying to do a select statement on three tables using the following code.
----------
Dim strSQL as string
Dim rsDataProp As Recordset

strSQL = "SELECT luc, description, units, view, "
strSQL = strSQL & "unittype, notes "
strSQL = strSQL & "FROM land AS dl "
strSQL = strSQL & "LEFT JOIN landuse AS lu ON (dl.luc = lu.code "
strSQL = strSQL & "LEFT JOIN landunit AS tlu ON (dl.units = tlu.code "
strSQL = strSQL & "WHERE dl.account = " & ??????????
strSQL = strSQL & " AND dl.card = " & ?????????
strSQL = strSQL & " AND seqnumber = 1"
Set rsDataProp = db.OpenRecordset(strSQL)
----------

When I run this code I receive a syntax error(missing operator) after the "ON (dl.luc = tlu.code)" code.

I know it has something to do with the parentheses around the join statements. I checked and tried a couple of suggestions that I found here on tek-tips.com but for some reason I just can't put it together.

For the time being I have broken it out into separate statements with there own recordsets but I would like to try to make this technique work.

Any help or suggestions would be appreciated.

 

[LucyP]

Access has its own weird syntax for multiple joins.

Go to query desgn view in the database, select the appropriate tables and let Access write the SQL for you (it'll give you inner joins, but they are easy enough to change later). Then copy and paste back into your code.

NB the problem is that you have to write all of the joins and then all of the on clauses together, but I will only make a mistake if i try to give you the sql.

 

[PHV]

strSQL = "SELECT luc, description, units, view, "
strSQL = strSQL & "unittype, notes "
strSQL = strSQL & "FROM (land AS dl "
strSQL = strSQL & "LEFT JOIN landuse AS lu ON dl.luc = lu.code) "
strSQL = strSQL & "LEFT JOIN landunit AS tlu ON dl.units = tlu.code "
strSQL = strSQL & "WHERE dl.account = " & ??????????
strSQL = strSQL & " AND dl.card = " & ?????????
strSQL = strSQL & " AND seqnumber = 1"
Set rsDataProp = db.OpenRecordset(strSQL)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[timb94]

Thanks LucyP. A star for you.

That worked perfect.

I don't know why I didn't think of that technique. I've used it before in going from Access to SQL but I didn't think about using it in Access itself.

This solved a number of problems for me.

Thanks again and have a great weekend.