I know. It is not MS Access. But it is VBA and I bet a lot of you use Excel as well. AND, it was fun to do.
This video series shows me recording a very basic VBA macro in Excel, and then modifying it to make it more complex. Excel is great for recording a macro, but you have to add things like loops manually. This series is mainly about the loop.
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.
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:
? dlookup(“[StartDate]”,”Courses”,”CourseName=’Basket Weaving 101′”)
? dlookup(“[CourseName]”,”Courses”,”StartDate=#Jan 26, 2015#”)
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