I was helping someone out on another thread. We found that the following query works fine
(A) when run in SQL View AND
(B) when run in VBA populating a DAO.Recordset.
However it throws a syntax error when populating an ADODB.Recordset, "Invalid bracketing of....". Now here's the crazy thing. It's apparently not a syntax error on my part - more likely Access is producing a syntax error when we save the query in the object pane (because Access adjusts the syntax when we save the query).
The reason I am tempted to blame Access is the following test. I tried saving the query string to a table and retriving it like this
SQL = DLookup("qryString"....
Guess what, when done this way it worked fine !!! To me this seems to confirm that the fault lies with how Access adjusts the syntax when we save the query in the object pane. I'm not trying to poo-poo Microsoft here. I'm asking if anyone sees a problem in the way I worded this query, perhaps I'm not following standards and thus contributing to the problem.
SELECT Connections.Important_0, Connections.[Detail0/4-Needed], Connections.[Detail0/1-Needed], A.FullName as [Detail0/2-Needed]
FROM
(
SELECT D.Important_0, FIRST(D.[Detail0/4-Needed]) as [Detail0/4-Needed], FIRST(D.[Detail0/1-Needed]) as [Detail0/1-Needed], FIRST(D.[Detail0/2-Needed]) as [Detail0/2-Needed]
FROM [Database] as D
WHERE Important_2 = @NumberToFind
GROUP BY Important_0
UNION ALL
SELECT D.Important_0, FIRST(D.[Detail0/4-Needed]) as [Detail0/4-Needed], FIRST(D.[Detail0/1-Needed]) as [Detail0/1-Needed], FIRST(D.[Detail0/2-Needed]) as [Detail0/2-Needed]
FROM [Database] as D
WHERE Important_1 = @NumberToFind
GROUP BY Important_0
) as Connections
LEFT JOIN AbbreviationsForDetails02Needed as A
ON A.Abbreviation = Connections.[Detail0/2-Needed]
As you can see, the above is based on two inner SELECTs unioned. These two SELECTS do not throw the error, as you can test by running them alone. Apparently it has to do with the outermost select?
Anyway if you want to test this for yourself, download the attached MDB file and type the following number into the search box on the Main Form.
90389940