SQL Statements For Beginners

SQL Statements For Beginners

You are here because you can build Queries with the Query Designer grid in MS Access, however you’ve started to realise that the Query Designer grid can’t do everything you want. A query is basically a string of text characters, often referred to as an SQL String. And that’s the key point about it, it is a string and VBA is very good at manipulating strings. VBA is excellent for manipulating strings and particularly SQL Strings (Statements) — in this set of videos I would like to show you you the basics, starting off with what you already know from your experience with the Query Designer grid… And the designer grid is a brilliant place to start because it produces SQL Statements for you! I rarely write an SQL Statement of the cuff, I open up MS Access and and use the Query Designer grid!

SQL Statements 1

SQL Statements 1

Video 1 (2:39)

In this video I demonstrate how to create a query, and then to extract the SQL Statement from the query. I demonstrate using the query in different “MS Access Forms” to demonstrate that a Table, a Query and the text version of a query (an SQL Statement) all work the same!

Video 1 (2:39)

Play Video

… …

SQL Statements 2

SQL Statements 2

Video 2 (8:22)

In this video I demonstrate how you can add “Like” to an SQL Statement to return a particular set a records. Next I transfer the form to another form as subform, and order it’s layout to show in datasheet view. Then I add a command button and and have the command and build the SQL Statement. However you hit the first problems with copying SQL Statement straight out of the designer grid, they can contain to-many speech marks to work properly in a string variable. Then I show you how you can “incorrectly” (it didn’t work) try and set the record source of the form you are in with VBA code. It doesn’t work because the record source you want to affect belongs to the subform contained within the main form (Form2).

Video 2 (8:22)

Play Video

… …

SQL Statements 3

SQL Statements 3

Video 3 (6:34)

In this video I’ve added a combobox in which I’ve hardcoded A, B, C, D, F, etc I run through the code showing you how the combo-box after update event passes its value through to a function. The function is designed to manipulate the SQL String to to create essentially a variable SQL Statement an SQL Statement which will contain A or B or C or D — depending on the selection you making the combo box. I explain the problem with hard coding the data in the combobox row source as a value list. There’s another problem to be aware of. Your code may not return all of your records, especially if the record contains a non-standard character. I demonstrate how you can use the query builder Grid along with the expression Builder to build a sophisticated query to extract the first letter of the field from the table. The query returns all of the records in other words lots of duplicates. The duplicates can be eliminated by adding the group by clause to the query. I hope you can see here the goal is to write code which needs no modification in the future. If you had stuck with a list of values in the combobox, then anytime a user entered a non-standard character, you would have had to to modify the combo box to take this non-standard character into account.

Video 3 (6:34)

Play Video

… …