32,556
Overview
One of the most popular (frequently occurring rather than best liked) problems we get here is with SQL strings being created/manipulated in VBA code. This is also true in other coding environments, but I'll work here with the VBA situation.
Explanation
The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code, which in turn is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.
Solutions
A technique I often suggest to coders struggling with this (Coders of any level - This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it. The benefit of the Immediate Pane option over the MsgBox() is that it can then be copied and pasted - into a post in Bytes for instance.
Another alternative, assuming you're relatively comfortable with Debugging in VBA, is simply to trace through (You can set up a breakpoint to do this) to the line that will execute the SQL string, then you can look at the string value directly (in various ways in Debug mode - including printing into the Immediate Pane).
A third thing to try, because sometimes the behaviour and error messages seen when running via VBA are different from those seen running a full QueryDef (Saved Query in an Access database), is to take the SQL printed and put it into a QueryDef object and seeing what happens when you try to run it. To do this it is only necessary to create a QueryDef and, when it is opened for design, switch to SQL view and paste your copied SQL string in place of what's already there. Run it from there to see what happens. You may, or may not, get more information that way than you get from trying to execute it from VBA.
Conclusion
It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety and in its true form, rather than as the code is about to create it.
This is also a much better way to ask SQL questions on a forum. No-one is going to appreciate being asked a SQL question about your posted VBA code.
