Can the CHOOSE function utilize a lookup table rather than hard coding the text into a query MS 2003

[K Roger]

Choose Function with a table. Within MS Access 2003



Is it reasonably possible (without extensive coding) to somehow utilize a lookup table with a CHOOSE () function instead of coding separate comma and quote separated text?

What I am attempting to do is to avoid writing and maintaining long character strings within several CHOOSE functions e.g. 2 - 24 choices within an option group? I am only working with subreports that are to be printed within a regular report.

Assuming there are no limits to the length of the string or number of characters within a function, such a function could be hard-coded into a query.

However, such code would be much easier to maintain with various lookup tables e.g. tables of 1...n sequential numbers with the appropriate option text.

I have looked online, in this forum and in several “Access bibles” and have found nothing with any detail other than a single undetailed reference suggesting it could be done without any suggestions as to how to actually do it.

Any helpful generalized hints or “example” code would be great. I would prefer to utilize a lookup table within a query, but if necessary would find code utilizing SQL or VBA helpful.

Thanks in advance for any and all assistance.

K Roger

[ranman256]

if you use a lookup table, then theres no reason to use the 'choose' fcn.
A query would do the lookup by joining your data table to it.

[Bulzie]

Can you give an example of your table, fields and a sample of data and how you want it to work? Might be an easier way to do things as ranman256 is suggesting.

[K Roger]

Can you give an example of your table, fields and a sample of data and how you want it to work? Might be an easier way to do things as ranman256 is suggesting.

Thanks for your initial response to this thread.

Here is more background information.

What I am trying to do is create individual plant labels, one label per collection site. A particular “run” of a report may include a dozen different sites (records) which would create 12 different labels.

The information is stored in Main table with such information as plant name, location of collection, date, person collecting and habitat information.

Then there are at least 3 sub-tables (each master record-to-many children linked by keys) that contain information about the individual plants collected at that site, such as reproductive status, size, color, substrate. etc. These fields differ from the main table and are thus kept in separate tables with multiple records for each main record.

I am here concerned only with translating option group numbers 1….x, with the appropriate text to be printed in reports. I am not attempting to update the source and/or sub-tables.

As an example. “Growth Habit” has an option group of ~ 24 combinations of Cushion, Tuft, Mat, Patches, abbreviated as C, T, M, P, C-T, C-P, T-M-P, C-T-M-P etc. etc.
For example, this option group is 1 = “Cushion”, 2 = “Mat”… 15 = “C-T” … 24 = “C-T-M-P”

My goal in this example is to print out the appropriate choice e.g. C-T, which appears in the option group as 15, rather than the option key“15”.

There may be as many as 10 such option groups to be processed and printed for each main, which using the DLOOKUP necessitates an equivalent number of lookup tables – all in 1 query?

I see several alternatives in coding in order to create this type of report.

1. With a wizard or building from SQL using DLOOKUP for each individual option group that temporarily creates table(s) from which a label can be created and printed using a report form, This involves a lot of tables and linkages, but the lookup tables are easier to build and maintain.
2. With a wizard or building from SQL using CHOOSE function that involves an extensive amount of hard code with the function. (easy to write but difficult to maintain) without using DLOOKUP
3. Running a DLOOKUP or CHOOSE function (within a VBA or SQL statement) for each field in a query) that “converts” each number to text that can then be used to build the report. eg, roughly-speaking [ABC]:CHOOSE ([DLOOKUP table option number XYZ, use this option number in lookup table to obtain text). Output text value as ABC
4. Creating the above (no. 3) within a Query Wizard.

I have not found any discussion or examples of the latter 2 options. However, they seem to be (logically) the most efficient use of time, space, linkages, coding, execution speed etc.

The problem is that the latter choice is syntactically beyond my current programming skills if it can be done at all at any level of expertise within ACCESS 2003?

All I need is some help with a) which is the best option and b) some syntax and/or programming examples using generic names for the variables, parameters. Any suggestions via online links are also quite welcome. Hopefully, you do not need the specific tables and field names. The general syntax should be sufficient for me to do the actual field by field coding.

Thanks
K Roger

[NTC]

well - developing today in the 2003 edition seems a shame - but I'll not go down that path. The topic doesn't appear edition related in any way.

From all that is posted I've gleaned on this one point: print out the appropriate choice e.g. C-T, which appears in the option group as 15, rather than the option key“15”.

When one uses an Option Group control - each option is given an integer value. That's the way those controls work. You need to have a table with 2 columns (at least) that is Integer and English - for now I'll call this the Translation Table but probably it means adding a field/column to one of your existing tables and adding the integer values. But that's just a guess on my part.

So at the presentation level (Report or Form) you want to display the English. You need to include the Translation table in your record set, via your query design, with the appropriate join.