Database - How can I find out why my SQL statement isn't working?

Quite often when you are use an SQL statement to load data from your database, you find that it does not work, or returns the wrong data.

In order to find out why you need to look at the SQL, which can be awkward if you are adding to it from variables/controls. This is especially true if you are putting the SQL directly into something like the open method of a recordset, eg:

Code:

---

rs.Open "SELECT * FROM myTable WHERE myField = " & Text1.Text, ..other parameters..

---

A good way to see the SQL (which is often recommend by various forum members) is to print it to the Immediate window of the VB IDE. To do this you need to first put the SQL into a string, then use Debug.Print to copy it to the Immediate window, eg:

Code:

---

Dim strSQL As String
strSQL = "SELECT * FROM myTable WHERE myField = " & Text1.Text

'copy to Immediate window
Debug.Print strSQL

rs.Open strSQL, ..other parameters..

---

You can then look at the Immediate Window (if you can't see it, select it from the View menu) to see the SQL, and hopefully spot the issue.

Once your SQL is in the Immediate Window you can also copy it to the query tool of your database to see if it works there, and hopefully see the issue.


If your SQL statement is quite complex, you may not be able to spot the issue yourself (and the error messages from the database query tool may not help you). If this is the case, it may be useful to see the SQL in a formatted way - which can be done using the code from this thread (run it in a separate project - just copy your SQL to it from the Immediate Window), and this will also point out a few of the more common mistakes.


If none of this helps, then feel free to post your SQL and any error messages (as well as other details such as the database system you are using) to the Database Development forum. There are several experts there who will probably find the issue quite quickly.