0

MS-Access removes brackets/parentheses around JOIN expression.

SELECT table1.id_field, table1.string_field 


FROM table1 LEFT JOIN table2 ON ( table1.field1 = table2.field1 
                                  AND table1.field2 = table2.field2  
                                  AND table1.field3 =[forms]![testform]![txt_hiddenmonth]  )

Is there a workaround for this error - JOIN expression not supported" error caused by unbracketed JOIN expression comprising string condition

Tom
  • 77
  • 10
  • Can you provide a little more detail? I don't even know where you have placed this query. – Robert Harvey Aug 05 '20 at 17:21
  • What is value in txt_hiddenmonth? Possibly put that last equality in a WHERE clause. – June7 Aug 05 '20 at 17:26
  • Access only removes parentheses and does funky stuff with your SQL if you use design view. If you write the entire query in SQL view and never switch to design view, it usually doesn't touch your SQL and queries like these should just work. – Erik A Aug 05 '20 at 17:27
  • @June7 That's a different query, since it's an outer join. Having a constant in an outer join makes perfect sense and is not easily replicated using a `WHERE` clause – Erik A Aug 05 '20 at 17:28
  • @ErikA, what do you mean by 'different query'? This is only a compound join of 2 tables. I have never seen a query object with a dynamic field reference in JOIN clause. If that is the intent here, then I think need VBA and QueryDefs to modify query object structure. – June7 Aug 05 '20 at 17:35
  • This is just a sample query that I am using. As @ErikA mentioned, access does funky stuff when using design view. The value for hidden_month is 3 for March. If I run the query, it works but when I close and save, access reformats the sql and causes error join not supported. – Tom Aug 05 '20 at 17:38
  • @June7 Access supports field references in normal queries (without VBA, executed through the GUI) just fine. If you involve VBA and QueryDefs (DAO), however, it becomes a different story and the query suddenly won't work. I've written about it [here](https://stackoverflow.com/a/49509616/7296893), it's a form-based parameter which works in the application, forms, and `DoCmd.RunSQL`. – Erik A Aug 05 '20 at 17:43
  • @Tom The solution to that should be simple: don't use design view. Access shouldn't reformat queries if they're created in SQL view and never enter design view. There are some oddities that can happen if you sort/filter through the GUI, which can often be solved by creating a new query instead of editing the one bugging out. – Erik A Aug 05 '20 at 17:44
  • @ErikA I understand that is a good workaround, however, I have multiple users that use this file and am worried that a user might open it in Design View – Tom Aug 05 '20 at 17:51
  • Well, there's no easy fix to preventing users from ruining your database. Some common solutions are giving users separate front-ends, using some form of version control, and using a separate dev database, then copying the data over before deploying or when problems occur. – Erik A Aug 05 '20 at 18:09
  • Ultimately, table 1 has 18 categories and table 2 has tests that have been completed for some categories. I need to list all 18 categories from table 1 and a count of how many tests in each category from table 2. When I run the sql (with the parentheses, it works, once I close and re-open the sql, access re-formats and removes the parentheses and the query does not return all 18 categories from table 1 if a test in table 2 has not been completed for that category. – Tom Aug 06 '20 at 15:43
  • I have posted a similar question https://stackoverflow.com/questions/63195144/tableau-ms-access-not-returning-correct-count – Tom Aug 06 '20 at 15:58
  • So is this a duplicate question? See my comment under answer in your other posting. – June7 Aug 06 '20 at 20:21

0 Answers0