<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Big Choices -- Dynamic columns with A Better Choose function

Published on
13,019 Points
3,319 Views
2 Endorsements
Last Modified:
Awarded

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
Comment
Author:aikimark
  • 2
4 Comments
LVL 52

Expert Comment

by:Dale Fye
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
0
LVL 48

Author Comment

by:aikimark
I agree.  This was not my problem and not my database, so I don't know the significance of 32 fields.  Maybe cost centers?
0
LVL 1

Expert Comment

by:isense
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.
0
LVL 48

Author Comment

by:aikimark
@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.
0

Featured Post

Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Join & Write a Comment

Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month