Using code to set a field source in a query

[JimP]

I have a field in a query named Field1 (e.g.). Can I use code to set the
source for the field?

e.g. The following works in the query grid, but I would like to create a
function to do the same.

Field1: Choose(Id, Source1, Source2,,,,,n)

 

[Allen Browne]

Jim, I'm not sure I follow your logic here.
You already have a function that works, i.e. Choose().

More importantly, if you have lots of fields that contain similar data, the
table is not designed correctly. Repeating fields always indicate that the
data is not normalized. IMHO, you would be much better off to spend a few
hours reading up about how to normalize the table, rather than trying to
create code to fix the problem with the table. Once it's normalized this
problem will cease to exist, i.e selecting the related record you need will
be a couple of seconds work (literally!)

You could start by asking Access to analyze the table for you. In Access
2007, Analyze Table is on the Database Tools tab of the ribbon. In previous
versions, its on the Tools menu.

Here's a bunch of links to get you started with the really important topic
of normalization:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[Jimp]

I was able to modify the SQL select statement using a querydef to get what
I needed.

This app is for a report (invoice) that has 6 user definable columns, that
may contain 1 of 40 different fields (based on whatever info a user wants to
show). Choose worked except for the 255 character limit. Perhaps there was
another way.

 

[Allen Browne]

Okay: now I follow: user-definable columns.
And Choose() has limited length.

I'm not sure exactly how you want this to work, but this function simulates
what Choose does:

Function MyChoose(ByVal iIndex As Integer, ParamArray varList()) As Variant
'Initialize to Null if nothing to return.
MyChoose = Null

'Make sure we got some values in the list
If IsArray(varList) Then
'User probably doesn't think zero-based
iIndex = iIndex - 1

'Check the index is in range.
If iIndex >= LBound(varList) And iIndex <= UBound(varList) Then
MyChoose = varList(iIndex)
End If
End If
End Function

 

[John Spencer]

Allen,
Code is good, HOWEVER Access will only accept a maximum of 29 arguments in a
function in a QUERY. Any more than that and you get a "The expression you
entered is too complex.". Also, even with 29 arguments you could run into
the 255 character limit depending on the lengths of the field names (average
field name length would need to be on the order of 7.5 characters).

The poster will probably have to resort to using VBA to build the query
string based on the user input.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..