Browse All Articles > Big Choices -- Dynamic columns with A Better Choose function

The Problem

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

Original question: http:Q_28476429.html
What doesn't work

Choose

```
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;
```

Switch

```
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;
```

```
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;
```

```
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
```

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.
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
```

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.
2. Table/query name

3. Row selection criteria

```
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)
```

```
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;
```

IIF( Choose()) -- the most limited

```
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;
```

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;
```

```
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;
```

```
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;
```

What next?

Fast Lookup Functions

http:A_1921-Access-Techniques-Fast-Table-Lookup-Functions.html

Store column names as strings in a table

Create a column name table

=-=-=-=-=-=-=-=-=-=-=-=-=-

Was this article helpful?

label that is just below and to the right of this text.

=-=-=-=-=-=-=-=-=-=-=-=-=-

## Comments (4)

Commented:

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

Author

Commented:Commented:

Author

Commented: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.