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

FYI... If I set a break point and click the search button the following line of code stops my compiler dead in its tracks...perhaps because I am running this in Access 2000.

Set Me.LBconnections.Recordset = Nothing

Error Message: Method or data member not found.

Regards,
Tim

It sounds to me like you just need to disambiguate all of your Dim statements.

...you just need to disambiguate all of your Dim statements.

That sounds plausible. Of course this should only be done in the privacy of your own home.

jal: I ran your sample in ac2007 putting numbers in the TextBox and I do not get any errors.

jal: I ran your sample in ac2007 putting numbers in the TextBox and I do not get any errors.
Well, that wouldn't surprise me a bit - that's a different engine, isn't it?
I think this may be a bug in 2003.

If I get a chance, I'll try it with acXP.

I tried it in ac2002 (XP) without an error. Am I supposed to do something besides put a number in the txtNumber control and push the button? If you have ac2003, have you applied SP3 yet and also the HotFix (http://support.microsoft.com/kb/945674)?

I tried it in ac2002 (XP) without an error. Am I supposed to do something besides put a number in the txtNumber control and push the button? If you have ac2003, have you applied SP3 yet and also the HotFix (http://support.microsoft.com/kb/945674)?
Thanks for all the effort. I have the service pack, I'll have to look into the Hotfix.

Anyway it's okay - our solution was to put the query string in a table and then load it dynamically at runtime. It was mostly out of curiosity that I opened this thread. I'll let you know if I make any progress on it.

I was getting this error on my home computer (Access 2003) and I just tried it at work - got the same error. Here's the full msg, which references a specific portion of the query string.


Invalid bracketing of name 'SELECT D.Important_0, FIRST(D.[Detail0/4-Needed'.

Is the actual field name Detail0/4-Needed or do you have two separate fields named Detail0 and Needed? If you have a field named Detail0/4-Needed, then you really should consider adopting a better field-naming convention.

Is the actual field name Detail0/4-Needed or do you have two separate fields named Detail0 and Needed? If you have a field named Detail0/4-Needed, then you really should consider adopting a better field-naming convention.

Those are the field names (the MDB file is in the original post) but not by my choice. I was helping someone else out on another thread. I don't know whether it was his choice, either.

Maybe I'll try renaming the columns to see if it clears things up.

In the code module for MainForm, try changing:
cmd.CommandText = qDef.SQL
...to:
cmd.CommandText = Replace( _
Replace(qDef.SQL, "[SELECT", "(SELECT"), _
"]. AS", ") AS")

In the code module for MainForm, try changing:
cmd.CommandText = qDef.SQL
...to:
cmd.CommandText = Replace( _
Replace(qDef.SQL, "[SELECT", "(SELECT"), _
"]. AS", ") AS")


Looks like a very nice solution. I'll give that a try. I wish Access had a menu with a set of options determining to what extent it is permitted to auto-adjust the syntax. I've never liked how Access rephrases my query - for one thing it always seems less readable.

In the code module for MainForm, try changing:
cmd.CommandText = qDef.SQL
...to:
cmd.CommandText = Replace( _
Replace(qDef.SQL, "[SELECT", "(SELECT"), _
"]. AS", ") AS")


Brilliant. Worked a treat !!!

I've seen something like this before and this is a wild guess but the [4-needed] may be interpreted as 4 minus the value in a colum called needed, If the Outer square brackets are stripped off.

Dennisk (http://www.access-programmers.co.uk/forums/member.php?u=19620),

At this point, that issue is moot. The REAL issue was the fact that the MS Access Query Builder was reformatting jal's in-line subquery, replacing:
(SELECT ... ) AS ...

...with:

[SELECT ... ]. AS ...


The []. was the convention used for in-line subqueries in A97, but was changed to use () with A2000 and up. However, their query-builder was apparently never updated to accommodate the new convention. Consequently, when making changes to a query with an in-line subquery in A2K+ with the query builder, it reformats the SQL statement with the []. when the changes are saved, even though the syntax is incorrect.

Dennisk (http://www.access-programmers.co.uk/forums/member.php?u=19620),

At this point, that issue is moot. The REAL issue was the fact that the MS Access Query Builder was reformatting jal's in-line subquery, replacing:
(SELECT ... ) AS ...

...with:

[SELECT ... ]. AS ...


The []. was the convention used for in-line subqueries in A97, but was changed to use () with A2000 and up. However, their query-builder was apparently never updated to accommodate the new convention. Consequently, when making changes to a query with an in-line subquery in A2K+ with the query builder, it reformats the SQL statement with the []. when the changes are saved, even though the syntax is incorrect.
Thanks for the history lession. So that explains why Access replaces my parantheses with square brakcets. I honestly had no idea.

This is more of a problem than I realized. I was just helping out a DIFFERENT person on another thread, with this query.

INSERT INTO CountOfEachVol
SELECT W.Vol, W.RecordID, CountForThisVol
FROM
(
SELECT Count(Vol) as CountForThisVol, Vol
FROM WELLS
GROUP BY Vol
) as CountOfEachVol
INNER JOIN Wells as W ON W.Vol = CountOfEachVol.Vol
ORDER BY W.Vol

The problem is that Access seems to use yet a different syntax for this one with the result that the solution above doesn't work - well it works in principle but I have to rewrite it according to this new syntax. How many syntaxes does Access 2003 use? Here's how Access renders it -notice the semicolon before the bracket, which is new. Also note that the period isn't present this time.

INSERT INTO CountOfEachVol
SELECT W.Vol AS Vol, W.RecordID AS RecordID, CountForThisVol AS CountForThisVol
FROM [SELECT Count(Vol) AS CountForThisVol, Vol FROM WELLS GROUP BY Vol; ] AS CountOfEachVol INNER JOIN Wells AS W ON W.Vol=CountOfEachVol.Vol
ORDER BY W.Vol;

Is life really this complicated?

I guess I can write one function that will handle all these situations.

jal (http://www.access-programmers.co.uk/forums/member.php?u=41799),

There is a simpler solution. Instead of storing the SQL statement in a Query, then using syntax like the following:
cmd.CommandText = qDef.SQL

...you can simply hard code the SQL statement into your program, like the following example:
cmd.CommandText = "SELECT T1.*" _
" FROM (SELECT MyField1, MyField2 FROM MyTable) AS T1"

jal (http://www.access-programmers.co.uk/forums/member.php?u=41799),

There is a simpler solution. Instead of storing the SQL statement in a Query, then using syntax like the following:
cmd.CommandText = qDef.SQL

...you can simply hard code the SQL statement into your program, like the following example:
cmd.CommandText = "SELECT T1.*" _
" FROM (SELECT MyField1, MyField2 FROM MyTable) AS T1"
I often do it that way but it takes extra time, and reduces readability which makes debugging harder. And often I introduce bugs when translating to the above syntax.

I am guessing virtually no one creates a long query that way. We usually start with "normal SQL", test it in SQL view, and then translate to VBA only if needed.

As I said, what I'd really like is for Access to leave my query the heck alone. The adjusted and/or compiled versions should be kept hidden, viewable only at my request.

Another beef is that I find it pretty ridiculous that even Access 2003 doesn't allow embedding comments into the SQL. This too would greatly increase readability.

Nonetheless Access is fantastic software. So who am I to complain?

I am guessing virtually no one creates a long query that way. We usually start with "normal SQL", test it in SQL view, and then translate to VBA only if needed.

On the contrary, more programmers than you think choose manually creating the SQL statement over using the query builder, particularly for more complex queries, and hard-coding them into the VBA program. This is not to say that they do not ever use the Query builder as a starting point, but I would like to point out that embedding the SQL statements in code provides a facility for better securing the queries when compiling your application into an MDE file (Queries can be exploited far more easily than compiled code).