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)

Nifty Access YouTube Thumb Nail
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)

Nifty Access YouTube Thumb Nail
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)

Nifty Access YouTube Thumb Nail
Play Video

… …

More Useful Stuff HERE:-

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.