VBA Settings – Part 2

This time we will look at how to colour code our VBA programming. I like to colour my comment lines with green text. Variables are red in my code.

Here is one of my 3 minute videos showing how I do it.

VBA Settings – Part 1

There are a variety of settings in MS Access that you will customize over time. This one, inside the VBA editor, is one of the most important. Requiring Variable Declaration is a real time saver, and gets rid of lots of “bugs” before they even get started.

Using the Immediate Window

The Immediate Window in MS Access is a great way to test the use of a function before using that function in a query or in VBA code. You start using the Immediate Window by pressing Ctrl-G on your keyboard.

Examples used in the video are:
? 5*8
? now
? dlookup(“[CourseName]”,”Courses”,”CourseID=2″)
? dlookup(“[StartDate]”,”Courses”,”CourseName=’Basket Weaving 101′”)
? dlookup(“[CourseName]”,”Courses”,”StartDate=#Jan 26, 2015#”)

IF Statements In A Query

Question:

In a query with 2 columns based on 2 fields in a table, how can I create a 3rd column that lists the minimum of the other 2 columns?

Answer:

Here is a simple list of sample data for this query.

 Field1      Field2      Smallest
----------------------------------
   5            7            5
   9            3            3
   8            7            7
   1            2            1

Field1 and Field2 come from the table. Smallest is determined by the query.

You might think that there would be an “IF” command that we use in the query. You are almost correct. The command is an “IIF”, or in full, Immediate IF. It is formally called a function and you will find it as part of the VBA programming language.

In Access, the iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

The syntax for the iif function is:

iif ( condition, value_if_true, value_if_false )

condition: the value that you want to test

value_if_true: the value that is returned if “condition” evaluates to TRUE

value_if_false: the value that is returned if “condition” evaluates to FALSE

Here is the query that we need:

Select Field1, Field2, iif(Field1 < Field2, Field1, Field2) as Smallest 
From Table1

Enjoy.

Cascading Queries in a Single Query

As promised, here is the query within a query solution that simplifies the last article’s idea.

We saw in my last post how a question like “Who was the first person to borrow a book?” could be solved by having a query use the results from another query. That required 2 actual queries saved separately. Now let’s see that same concept done in a single saved query.

Let’s break the problem down into steps.

Step 1 – determine the earliest case of a book being borrowed

SELECT Min(tblBorrowing.dtmDateDue) AS EarliestDate
FROM tblBorrowing
WHERE (((tblBorrowing.lngBookID)=1));

I have shown the raw SQL above (good idea to learn that stuff!), but you could have just used the query builder to create a query like this following.

sub-select query part 1

Note my use of a custom field name in “EarliestDate”.

Step 2 – Utilize the first query in a where clause

final sub-query design

Note how I went back and created the original query style, utilizing all the tables and desired fields, and then added the first query as a criteria for the date field.

This creates a single saved query, rather than 2 separate ones. Here is the resulting (and somewhat hard to read!) SQL code.

SELECT tblCustomers.strCustomerName, tblBooks.strBookName, 
tblBorrowing.dtmDateDue
FROM (tblBooks INNER JOIN tblBorrowing ON tblBooks.lngBookID = 
tblBorrowing.lngBookID) INNER JOIN tblCustomers ON 
tblBorrowing.lngCustomerID = tblCustomers.lngCustomerID
WHERE (((tblBooks.lngBookID)=1) AND tblBorrowing.dtmDateDue = 
(SELECT Min(tblBorrowing.dtmDateDue) AS EarliestDate
FROM tblBorrowing
WHERE (((tblBorrowing.lngBookID)=1));
));

I will admit, however, that I often clutter my databases with the 2 (or more!!!) query solutions as discussed last week. I find them easier to comprehend and fine tune.

If you want a cleaner looking Queries tab, use this sub-select version!