Avoid IIf(), Switch(), and Choose() in VBA code

This is an important point to make ADezii.
However, I think to avoid these quite useful functions altogether simply because they can, in certain limited circumstances, noticeably degrade the performance of a project, is possibly overkill.
These limitations are certainly worth bearing in mind, especially if the various options execute large amounts of, or particularly slow, code.

On your last point, this is very interesting and I'd be very interested in a link to allow me to investigate further. I was unaware of any such difference in the SQL versions of these functions.

This is an important point to make ADezii.
However, I think to avoid these quite useful functions altogether simply because they can, in certain limited circumstances, noticeably degrade the performance of a project, is possibly overkill.
These limitations are certainly worth bearing in mind, especially if the various options execute large amounts of, or particularly slow, code.

On your last point, this is very interesting and I'd be very interested in a link to allow me to investigate further. I was unaware of any such difference in the SQL versions of these functions.

I must confess that I was a little hesitant about this particular Tip since I know that you are quite fond of these 3 Functions. Please don't take it personally, I just find the details relating to them very interesting.

NOTE: The reference source for this particular Tip is The Access 2002 Desktop Developer's Handbook by Paul Litwin, Ken Getz, and Mike Gunderloy. The specific location is Chapter 15 - Application Optimization, Test 17: Watch Out for IIf - It Doesn't Short-Circuit.

Personally, I think you were right to make the point my friend.
The emphasis may have been over-strong, but considering the target audience, it's an important point that people should, at least, be aware of.
There are certainly situations where I wouldn't dream of using them (Actually, though I was aware of the IIf() problem I didn't realise it covered Choose() & Switch() too).
People with very large databases who are looking to use the Domain Aggregate functions within one of these functions should be particularly cautious. I only use them with either very small datasets or when it is impossible to get the same functionality using a sub-query in SQL (which is very rare).

It's a shame you found it in a book as I wanted to read through it myself (easily - you know, on the web).

Tip #3 should be: How to use the help files.

Yeah, but they change so drastically for each version that would be a tome in itself :D

Yeah but some things are common like if you highlight a function and press F1 it will bring up the help file for it. Or if you don't know what a property does you can focus on the property and press F1 to bring up that help file. And how these things bring up different help files depending on if you're in Access or the Visual Basic Editor. And how they should look look at the See Also list for related topics.

Very fair point.
I was hoping to find time to do one on debugging at some stage. Please feel free to take this one on if you feel inclined. I agree that it may well be very helpful and save many explanations - if we could say RTFM (Or the help file equivalent) while pointing them at such a helpful (excuse the pun) Tutorial.

NEO I thought I read about this in the helpfile so I was in my VBA just now an typed in IIF, Switch, and Choose highlighted it hit F1 and each one was in there the point ADezii is making is right near the bottom. :)

OK I looked it up just for fun it is on the web on the Microsoft Site.

Developer Handbook Chapter 15

NEO I thought I read about this in the helpfile so I was in my VBA just now an typed in IIF, Switch, and Choose highlighted it hit F1 and each one was in there the point ADezii is making is right near the bottom. :)

Cheers Denburt.
I checked out the helpfile as you said, and the bit about evaluating both sides is there. What isn't, is the bit about it not being the same in the SQL engine. In other words, if you use it in a query, you will not suffer from the same drawback.
I've been aware of the full evaluation bit for a while, but the SQL bit was new to me and, I admit, a bit of a surprise. It's good that we can learn so much new stuff here :)

OK I looked it up just for fun it is on the web on the Microsoft Site.

Developer Handbook Chapter 15

Checking your new link out now...

It's good that we can learn so much new stuff here

So true I am still learning every day and I have been doing this for so long I have forgotten more than a lot of Access users will probably ever know so it is good to see refreshing bits like this.

I found what I was looking for in your link.
It was the Note under Test 17.

Note: The IIf function used in queries, forms, or reports doesn't have this same behavior—that is, it works as you'd expect it to in those places. It's only in VBA code, in a module, where you need to avoid IIf because of its overzealous evaluation tendencies.

Unfortunately, I actually expect it to evaluate the expressions fully before running :s

O.K. Found this, It can dog your queries down too if it is in a nested query.
At the bottom of this link although the whole page is worth reading (or reviewing):
Microsoft Jet Database Engine Programmer's Guide - Chapter 4

Common Pitfalls

Many common mistakes can cause unnecessary bottlenecks when you are running queries. Following is a list of these common mistakes and what to do to correct them.
•

Including expressions in query output. Placing expressions, such as an IIf on an output field of a query can cause optimization problems if the query is used as the input to another query. For example, consider the following two queries, where the first query is saved as Q1:

SELECT IIf(ShipVia = 2, 'United Package', 'Other') AS ShipperName FROM Orders; SELECT * FROM Q1 WHERE ShipperName = 'United Package';

Because Microsoft Jet can't optimize the IIf expression in the first query, it can't optimize the second query. Expressions can get buried so far down in a query tree that it's easy to forget about them. If expressions are necessary in the output, try to place the expression in a control on a form or report.

The following query replaces the previous two queries and is the optimal way to write this query:

SELECT * FROM Orders WHERE ShipVia = 2;

•

Using GROUP BY on too many fields. When you're creating a totals query, use the GROUP BY clause on as few fields as necessary to achieve the query's goal. The more fields in the GROUP BY clause, the longer the query takes to execute. Make sure you don't include extraneous fields.
•

Using GROUP BY in same query as a join. If possible, place a GROUP BY clause on a table and then join it to another table, rather than joining the two tables and using the GROUP BY clause in the same query as the join. For example, instead of this query

SELECT Orders.CustomerID, Count(Orders.OrderID) AS CountOfOrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Orders.CustomerID;

you may benefit by using two separate queries, as follows, where the first query is saved as SelectWithGroupByQ1:

SELECT Customers.CustomerID FROM Customers GROUP BY Customers.CustomerID; SELECT Orders.CustomerID, Count(Orders.OrderID) AS CountOfOrderID FROM SelectWithGroupByQ1, INNER JOIN Orders ON Q1.CustomerID = Orders.CustomerID GROUP BY Orders.CustomerID;

•

Not including indexes on join fields. When you're joining tables, always try to index the fields on both sides of a join. This can speed up query execution by allowing more sophisticated join strategies such as index and index-merge joins. The use of indexes also provides better statistics. For more information, see the "Join Strategies" section earlier in this chapter.
•

Under-indexing fields. In appropriate circumstances, for example where the database is used solely for decision-support types of applications, you can place an index on all fields that have unique values and are used in a join or as a restriction. By using Rushmore query optimization, Microsoft Jet can take advantage of multiple indexes on a single table. This makes indexing many fields advantageous. However, keep in mind that adding indexes to fields can cause performance to suffer.
•

Using Count(fieldname) instead of Count(*). When you have to determine the number of records, you should use Count(*) rather than Count(fieldname) because there are Rushmore optimizations that allow Count(*) to be executed much more quickly than Count(fieldname).