Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Big Choices -- Dynamic columns with A Better Choose function

aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT
Published:

Introduction

When answering a recent question, the testing of my solution taught me something about the VB language and some limitations in Access queries. This article shows you how to get past the 29 parameter limit in the Choose() function -- A Better Choose -- and how the variable column capability can add flexibility to your Access queries.


The Problem

The problem we are trying to solve, or functionality we want to introduce, is the ability to get different field values based on the numeric value of another field in the row.  Although the original question referenced fields with numeric names (D1, D2, D3, etc.), it would be nice to be able to select fields with any names.  Of course, the simplest solution for sequential number-named columns would look like this:

Select ID, DLookup("D" & Value, "MyTable", "ID=" & ID) As ValueChoice
                      From Mytable

Open in new window

Original question: http:Q_28476429.html


What doesn't work

Before getting to the solution, it might help to understand what does not work and why.


Choose

There is a 29 parameter limit.  If you have more than 28 columns, you will not be able to use the Choose function.  Here is an example of the Choose() function maxed out.  If I added one more field, I will get the 3075 error message: Expression too complex in query expression.

SELECT Top 1 Choose(ID,C011,C042,C303,C084,C005,C016,C777,C008,C009,C010,
                      C111,C012,C013,C014,C015,C016,C017,C018,C019,C020,
                      C021,C022,C023,C024,C025,C026,C027,C028) AS ValueChoice
                      FROM BigTable;

Open in new window


Note: In any code example, assume that the lines are continued or that line continuation does not matter due to the environment (Access query run-time).


Switch

Like the Choose() function, we are limited to 29 parameters.  Since the function requires pairs of (condition expression, value) parameters, the number of possible columns is halved.  The following is a maxed-out example of the Switch() function.

SELECT Top 1 Switch(ID=1,C001, ID=2,C002, ID=3,C003, ID=4,C004, ID=5,C005, 
                      ID=6,C006, ID=7,C007, ID=8,C008, ID=9,C009, ID=10,C010, 
                      ID=11,C011, ID=12,C012, ID=13,C013, ID=14,C014) As ValueChoice
                      FROM BigTable;

Open in new window

 

Split() function in the query

It would be so convenient if we could use the Split() function in our Access queries.  Imagine how much easier our queries would be if we could use Split() instead of Left,Right,Mid,Instr functions.  Unfortunately, the Access query engine does not like these attempts at direct string parsing:

SELECT split("Id District County Est_Id State_Id")(0) FROM Inspections;
                      
                      SELECT split("Id,District,County,Est_Id,State_Id", ",",-1,0)(0) FROM Inspections;

Open in new window



The Best Solution -- A Better Choose function

The solution is to implement our own Choose() function.  In order to mimic the VB Choose() function, we create our own VBA user-defined function, ABChoose() -- A Better Choose function.  We need a parameter for the numeric selection of the list of fields that will follow.  For the list of fields, we will use the ParamArray keyword.

Public Function ABChoose(ByVal parmWhichField, ParamArray parmFields())
                          'A Better Choose Function
                          Select Case parmWhichField
                              Case 1 To UBound(parmFields) + 1
                                  ABChoose = parmFields(parmWhichField - 1)
                              Case Else
                                  ABChoose = Null
                          End Select
                      End Function

Open in new window

 Since the array of fields is zero based and the first parameter of the function expects an ordinal selection value (one based), we have to adjust our bounds checking and item retrieval accordingly.
 

The Other Solution(s)

Create a string splitting function
Although you can not use the SPLIT() function in your Access query, you can address individual elements of the resultant array with a user-defined function.
 
Public Function GetNthFieldName(ByVal parmValue, ByVal parmFields As String, Optional parmBase = 1)
                          If parmBase = 1 Then
                              GetNthFieldName = Split(parmFields, ",")(parmValue - 1)
                          Else
                              GetNthFieldName = Split(parmFields, ",")(parmValue)
                          End If
                      End Function

Open in new window


Note: The optional parmBase parameter determines whether we are referencing the SPLIT() function result in an ordinal manner (1st, 2nd, 3rd, etc.) or whether as an absolution position (base zero).

Invoke the new function as the first parameter of a DLookup() function
The DLookup() function has three parameters.
1. Field/Column name
2. Table/query name
3. Row selection criteria
In this example, we are going after the 12 field in the "Inspections" table where the ID value is the value of our current row's ID field.

DLookup(GetNthFieldName(12,"Id,District,County,Est_Id,State_Id,Request_Number,Ehs_Id,Ehs,Territory,Est_Type,Insp_Type,Insp_Date,Extra_Credit,Grade,Score_SUM,Final_Score_SUM,Seats,Inspection_Time_Hrs,Inspection_Time_Min,Sample,Setup_Date,Update_Date,Update_User_Id,Water,Sewage,Origin,Permit_Status_Id,Permit_Status")
                      ,"Inspections","ID=" & ID)

Open in new window


Using this as your solution

Instead of Choose(), Switch(), or a hybrid using IIF(), we can now do this.

Select ID, DLookup(GetNthFieldName(12,"Id,District,County,Est_Id,State_Id,Request_Number,Ehs_Id,Ehs,Territory,Est_Type,Insp_Type,Insp_Date,Extra_Credit,Grade,Score_SUM,Final_Score_SUM,Seats,Inspection_Time_Hrs,Inspection_Time_Min,Sample,Setup_Date,Update_Date,Update_User_Id,Water,Sewage,Origin,Permit_Status_Id,Permit_Status")
                      ,"Inspections","ID=" & ID)
                      From Mytable;

Open in new window


Hybrid Solutions

There are a couple of solutions that would get around the 29 parameter limit of the Choose() function.


IIF( Choose()) -- the most limited

There is a limit of 7-10 nested levels in an IIF() function.  This might theoretically give us 196 (=7*28) selectable columns.  Unfortunately, the combination of Choose() functions almost eliminates the IIF nesting capability.  The following fifty-column example is too complex.

SELECT top 1
                      IIF([parmFieldnum]<=25, Choose([parmFieldnum],B.C001, B.C002, B.C003, B.C004, B.C005, B.C006, B.C007, B.C008, B.C009, B.C010, B.C011, B.C012, B.C013, B.C014, B.C015, B.C016, B.C017, B.C018, B.C019, B.C020, B.C021, B.C022, B.C023, B.C024, B.C025), 
                        IIF([parmFieldnum]<=50, Choose([parmFieldnum]-25, B.C026, B.C027, B.C028, B.C029, B.C030, B.C031, B.C032, B.C033, B.C034, B.C035, B.C036, B.C037, B.C038, B.C039, B.C040, B.C041, B.C042, B.C043, B.C044, B.C045, B.C046, B.C047, B.C048, B.C049, B.C050)))
                      FROM BigTable AS B;

Open in new window

Therefore, we can only select up to 52 (max) columns with a non-nested IIF statement.

SELECT TOP 1
                      IIf([parmFieldnum]<=26,Choose([parmFieldnum],B.C001,B.C002,B.C003,B.C004,B.C005,B.C006,B.C007,B.C008,B.C009,B.C010,B.C011,B.C012,B.C013,B.C014,B.C015,B.C016,B.C017,B.C018,B.C019,B.C020,B.C021,B.C022,B.C023,B.C024,B.C025,B.C026),
                      Choose([parmFieldnum]-26, B.C027,B.C028,B.C029,B.C030,B.C031,B.C032,B.C033,B.C034,B.C035,B.C036,B.C037,B.C038,B.C039,B.C040,B.C041,B.C042,B.C043,B.C044,B.C045,B.C046,B.C047,B.C048,B.C049,B.C050,B.C051,B.C052)) AS ValueChoice
                      FROM BigTable AS B;

Open in new window

 

Switch( Choose())

Even with the 28 pairs of parameters, we've more than quadrupled the IIF() hybrid limit above.  My test table only has 200 columns and with the 256 column limit, we will be able to select all the columns that our queries can provide.  In the following example, I am breaking down the columns into sets of 25.  With 14 pairs of Switch() parameters and 28 Choose() parameters, we can potentially select 392 (=14*28) columns.

SELECT TOP 1 Switch([parmFieldnum]<=25,Choose([parmFieldnum],B.C001,B.C002,B.C003,B.C004,B.C005,B.C006,B.C007,B.C008,B.C009,B.C010,B.C011,B.C012,B.C013,B.C014,B.C015,B.C016,B.C017,B.C018,B.C019,B.C020,B.C021,B.C022,B.C023,B.C024,B.C025),
                      [parmFieldnum]<=50,Choose([parmFieldnum]-25, B.C026, B.C027,B.C028,B.C029,B.C030,B.C031,B.C032,B.C033,B.C034,B.C035,B.C036,B.C037,B.C038,B.C039,B.C040,B.C041,B.C042,B.C043,B.C044,B.C045,B.C046,B.C047,B.C048,B.C049,B.C050),
                      [parmFieldnum]<=75,Choose([parmFieldnum]-50, C051, C052, C053, C054, C055, C056, C057, C058, C059, C060, C061, C062, C063, C064, C065, C066, C067, C068, C069, C070, C071, C072, C073, C074, C075),
                      [parmFieldnum]<=100,Choose([parmFieldnum]-75, C076, C077, C078, C079, C080, C081, C082, C083, C084, C085, C086, C087, C088, C089, C090, C091, C092, C093, C094, C095, C096, C097, C098, C099, C100),
                      [parmFieldnum]<=125,Choose([parmFieldnum]-100, C101, C102, C103, C104, C105, C106, C107, C108, C109, C110, C111, C112, C113, C114, C115, C116, C117, C118, C119, C120, C121, C122, C123, C124, C125),
                      [parmFieldnum]<=150,Choose([parmFieldnum]-125, C126, C127, C128, C129, C130, C131, C132, C133, C134, C135, C136, C137, C138, C139, C140, C141, C142, C143, C144, C145, C146, C147, C148, C149, C150),
                      [parmFieldnum]<=175,Choose([parmFieldnum]-150, C151, C152, C153, C154, C155, C156, C157, C158, C159, C160, C161, C162, C163, C164, C165, C166, C167, C168, C169, C170, C171, C172, C173, C174, C175),
                      [parmFieldnum]<=200,Choose([parmFieldnum]-175, C176, C177, C178, C179, C180, C181, C182, C183, C184, C185, C186, C187, C188, C189, C190, C191, C192, C193, C194, C195, C196, C197, C198, C199, C200)
                      ) AS ValueChoice
                      FROM BigTable AS B;

Open in new window

 

Choose(value, Choose(), Choose(), ...)

We can double the Switch() hybrid limit above.  In order to implement this, we will need to increase the complexity of our math calculations.  We only have a single nesting level of Choose() functions.  All of the Choose() functions inside this outer-most Choose() function have the same number of columns.  We do an integer division to point us to one of the contained Choose() functions.  Within each Choose() function we invoke a Mod function to select the column within that set.  In the following example, we have divided my 200 column table into 25 sets of 8 column 'sets'.  I used a query parameter to allow me to easily test the math.  Since each column set can be contain 28 columns and we have three more column sets that we can add, we have a theoretical capacity to select 784 (=28*28) columns/value.  The following example is not written in as compact a form as possible, since we could have used 8 sets of 25 column names.  I wanted you to see that nesting Choose() functions would double the capacity of the Switch( Choose()) hybrid solution.

SELECT TOP 1 Choose((([parmFieldnum]-1)\8)+1,
                      Choose((([parmFieldnum]-1) mod 8)+1, C001,C002,C003,C004,C005,C006,C007,C008),
                      Choose((([parmFieldnum]-1) mod 8)+1, C009,C010,C011,C012,C013,C014,C015,C016),
                      Choose((([parmFieldnum]-1) mod 8)+1, C017,C018,C019,C020,C021,C022,C023,C024),
                      Choose((([parmFieldnum]-1) mod 8)+1, C025, C026 ,C027,C028,C029,C030,C031,C032),
                      Choose((([parmFieldnum]-1) mod 8)+1, C033,C034,C035,C036,C037,C038,C039,C040),
                      Choose((([parmFieldnum]-1) mod 8)+1, C041,C042,C043,C044,C045,C046,C047,C048),
                      Choose((([parmFieldnum]-1) mod 8)+1, C049, C050, C051, C052, C053, C054, C055, C056),
                      Choose((([parmFieldnum]-1) mod 8)+1, C057, C058, C059, C060, C061, C062, C063, C064 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C065, C066, C067, C068, C069, C070, C071, C072 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C073, C074, C075, C076, C077, C078, C079, C080 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C081, C082, C083, C084, C085, C086, C087, C088 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C089, C090, C091, C092, C093, C094, C095, C096 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C097, C098, C099, C100, C101, C102, C103, C104 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C105, C106, C107, C108, C109, C110, C111, C112 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C113, C114, C115, C116, C117, C118, C119, C120 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C121, C122, C123, C124, C125, C126, C127, C128 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C129, C130, C131, C132, C133, C134, C135, C136 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C137, C138, C139, C140, C141, C142, C143, C144 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C145, C146, C147, C148, C149, C150, C151, C152 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C153, C154, C155, C156, C157, C158, C159, C160 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C161, C162, C163, C164, C165, C166, C167, C168 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C169, C170, C171, C172, C173, C174, C175, C176 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C177, C178, C179, C180, C181, C182, C183, C184 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C185, C186, C187, C188, C189, C190, C191, C192 ),
                      Choose((([parmFieldnum]-1) mod 8)+1, C193, C194, C195, C196, C197, C198, C199, C200)
                      ) AS ValueChoice
                      FROM BigTable AS B;

Open in new window



What next?

You now have the basic ability to employ the ABChoose() function, or the GetNthFieldName() function, to selectively retrieve the value of any column out of a list of column names by some numeric value.  While this does solve the original problem, it is not the end of possible improvements to this soution.


Fast Lookup Functions

Harfang has an excellent article on fast lookups -- faster than DLookup().  It is possible that the flexible column choice functionality can be combined with his fast lookup.  However, I will leave this to the reader, encouraging them to write about it.
http:A_1921-Access-Techniques-Fast-Table-Lookup-Functions.html


Store column names as strings in a table

For simple cases, typing the column names might not be too much to ask for the power and flexibility we gain.  However, there may be queries where you need to do invoke this for many columns.  In such cases, it would help to have a table to store our column names.  We could join the column name table to our query or we could pass the key of the row of the column name table to our splitting function.


Create a column name table

In this configuration, we have one row for every column name or set of columns.  Rather than using the Split() function, we invoke a query to retrieve the name of the column based on a numeric key. This table can also be joined to the tables in our main/parent query.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.
 
If you found this article helpful, please click the Yes button near the:
 
      Was this article helpful?
 
label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
2
4,366 Views
aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT

Comments (4)

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
I love the use of parameter arrays in UDF's.  I do this all the time, but had not thought to do it as a replacement for the Choose or Switch functions.

I'm just thinking that you have to be working with a really poorly normalized database to need that level of nesting just to identify the field you want to select.  I believe I had to deal with that many fields, I'd write the SQL string dynamically.

Dale
aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT
Top Expert 2014

Author

Commented:
I agree.  This was not my problem and not my database, so I don't know the significance of 32 fields.  Maybe cost centers?
Martyn KenyonMD and Consultant

Commented:
It was my original problem.  The database was a corporate database for one of my clients.  The database has a record for each month and within the record there is a field for each day of the month (31) and a total.
aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT
Top Expert 2014

Author

Commented:
@Dale

I posted the (much) simpler solution in the original question thread.  The fact that these fields were sequentially numbered facilitated the dynamic column name creation via simple concatenation inside of a DLookup() function.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.