A 2010 / SQL / Select (1 Viewer)

While studying SQL I built a simple query in QBE grid, and if I hit run while in design view or n SQL view it, or just double click the query name it runs fine, but, if I paste the SQL statement on a cmd button it give me a syntax error. Is there some reason that I can't run a SQL from a cmd Button?

When pasting SQL into a procedure or function in VBA, you have to remember that it is a string variable, and as such needs to be placed inside quote delimiters. So, what looks like this in the QBE;


Looks like this in VBA;


Additionally, if you have a reference to a form control in the criteria of the query, then that reference needs to be outside the string (not inside the quotes in other words) when building the SQL string in code;


I mention this because it's not uncommon to have a form control reference like this and is a common problem people encounter when they are learning how to properly build SQL strings in code. It's not just cut/paste and you're good to go. You have to adjust it a bit.

Beetle said:

When pasting SQL into a procedure or function in VBA, you have to remember that it is a string variable, and as such needs to be placed inside quote delimiters. So, what looks like this in the QBE; .

Click to expand...

Sean,
I have uploaded the learning db I made, it is called SQL Seminar. Same link, email, and password. I would appreciate it if you would code the cmdTestSQL which I have the SQL to filter the form, and send it back to me. I think I can study it and apply it to real world db’s. I have spent about the last three hours listing to YouTube Videos on SQL. I think I know why I am such a slow learner. I think I have a faulty [Dim Thirsty As String]

Beetle said:

Additionally, if you have a reference to a form control in the criteria of the query, then that reference needs to be outside the string (not inside the quotes in other words) when building the SQL string in code;

Code:Copy to clipboard

Dim strSQL As String
 
strSQL = "SELECT Table1.FieldA, Table1.FieldB FROM Table1 " _
       & "WHERE Table1.FieldA = " & Forms!frmMain!txtMyTextBox

Click to expand...

Not quite.

It is true for CurrentDb.Execute because the database engine does not have scope of Access objects such as forms.

However if the SQL is run using DoCmd.RunSQL it can include direct references to Access objects because RunSQL is handled by Access before being passed to the database engine.

Execute is preferred by experienced developers because it doesn't trigger warnings while many beginners use RunSQL because it is easier to write.

RunSQL handles the datatypes automatically and can deal with quote marks in the control values that can break the concatenated version.

@Galaxiom

You're right. I forget that sometimes because I never use RunSQL.