creating dynamic rowsource query

[JJSRich]

I am trying to build up filter to grab one piece of information (quote_id) and use that to populate a combobox. There are a few fields that can be used, Distributor, Builder, First line of address (haven't got to yet). I use a tickbox next to each field to selectively enable/disable portions of the query. Anyhow, here's my code as it stands.

oldSource = "SELECT DISTINCT h.quote_id"
oldSource = oldSource & " FROM HouseType AS h INNER JOIN quotations AS q ON h.quote_id = q.quote_id "

If Me.DistFilter = True Then
 oldSource = oldSource & " LEFT JOIN companies AS c1 ON q.dist_id = c1.Comp_ID"
End If
If Me.BuildFilter = True Then
 oldSource = oldSource & " LEFT JOIN companies AS c2 ON q.build_id = c2.Comp_ID"
End If

oldSource = oldSource & " WHERE h.design_production_no Is Not Null"

If Me.DistFilter = True Then
 oldSource = oldSource & " AND Nz([c1].[Company], [q].[distributor]) LIKE [forms]![FindSE].[DistList]"
End If
If Me.BuildFilter = True Then
 oldSource = oldSource & " AND Nz([c2].[Company], [q].[builder]) LIKE [forms]![FindSE].[BuildList] "
End If)
End If

oldSource = oldSource & " ORDER BY h.quote_id DESC;"

when run, the rowsource contains a syntax error. I have created the complex version of the query, which shows up as

SELECT DISTINCT q.quote_id
FROM ((housetype AS h INNER JOIN quotations AS q ON h.quote_id = q.quote_id) LEFT JOIN companies AS c2 ON q.build_id = c2.Comp_ID) LEFT JOIN companies AS c1 ON q.dist_id = c1.Comp_ID
WHERE (((h.design_production_no) Is Not Null) AND ((Nz([c1].[Company],[q].[distributor])) LIKE [forms]![FindSE].[DistList]) AND ((Nz([c2].[Company],[q].[builder])) LIKE [forms]![FindSE].[BuildList]));

in the query editor. I am in parenthesis hell here, and I don't know how to get out.

 

[Remou]

Most parentheses created by the query builder are pointless, however, the ones used for joins are necessary. This means that you will need to check before you build this line:

[tt]oldSource = oldSource & " FROM HouseType AS h INNER JOIN quotations AS q ON h.quote_id = q.quote_id "[/tt]

Whether or not you will need the parentheses for further joins, that is, DistFilter and BuildFilter, at one parenthesis per join.

http://lessthandot.com

 

[JJSRich]

I had a think, and do I really need to do dynamic joins? They are left joins, with a dynamic where clause as well. As long as my where clauses have their parenthesis in the right places, I should be ok, right? My resultset isn't any smaller when I leave out all dynamic where clauses, and remove their joins as when I leave the joins in without any dynamic clauses.

I've got to build the same thing all over again, joining the same tables in a different way to get a different set of results, but still filtered as the user demands. I am confusing myself, but I think I almost have it figured out.

 

[Remou]

You certainly seem to have a lot of joins, so you could probably live without a few , for the most part, filters on the numbers (quote_id etc) are going to be available, so the joins will not be needed.

http://lessthandot.com

 

[JJSRich]

That's nothing, you should see the 30+ lines of SQL that I've got in other places, joining across 4 tables and several table aliases, or some of my stored procedures or views. I really really dislike working with Access queries, writing MySQL is so much easier. Pity my end-users can't just do everything in SQL, my work would be done.

The joins are there because I don't store company names in the quotes table, but need them, and sometimes I have a Design Production Number, other times I don't and programatically create it, but need to have a distinction when this is the case. Fun, fun databases.