MS Access 2010 dynamically reference query column in IIF Statement using DLookup

I'm trying to develop a report that can be changed based on a settings table.  This way the report can be reused in different settings.

The column in the report shows a date that someone completed a task.  There is a hidden column in the report "Responsible" that shows a list of people that are responsible for that task; multiple people that can have a task with an assignment.  If they are not responsible for the task, the report says N/A.  If they have the task and it's not complete, then it needs to be blank (meaning needing focus).  The date complete appears if the person is responsible and has completed the task.  All of this is working when you have a predefined list of folks.

I would like to compile and make my application a accde but that locks the report changes (of course).  So I created a settings table so I could perform a look up on which columns to display.  This could allow for changes to the report even reordering of the person showing up.  

The issue that I'm having is that I'm trying to change the control source to reference my settings table.  I get the IIf evaluation to function correctly, but I am not able to dynamically reference the column name in the query.  Here is the code that is marginally working:

Here is what I've tried:
=IIf(InStr(1,[Responsible],DLookUp("Description","settings","[Type] ='RLabel-1'"))>0,Val('[' & DLookUp("Description","settings","[Type] ='RLabel-1'") & ']'),"N/A") --> Returns 0

=IIf(InStr(1,[Responsible],DLookUp("Description","settings","[Type] ='RLabel-1'"))>0,Eval('[' & DLookUp("Description","settings","[Type] ='RLabel-1'") & ']'),"N/A") --> Returns [Bob] as a text string not the reference in the query [Bob]  (i.e., [query].[Bob])

=IIf(InStr(1,[Responsible],DLookUp("Description","settings","[Type] ='RLabel-1'"))>0,'[' & DLookUp("Description","settings","[Type] ='RLabel-1'") & ']',"N/A") --> Returns [Bob] as a text string (same as using double quotes) not the reference in the query [Bob]  (i.e., [query].[Bob])

=IIf(InStr(1,[Responsible],DLookUp("Description","settings","[Type] ='RLabel-1'"))>0,[ & DLookUp("Description","settings","[Type] ='RLabel-1'") & ]),"N/A") --> Unfortunately... Access indicates this is not correct syntax

I'm not sure that I can reference this in the codebehind, because the report is outputted to pdf directly then attached to an email... previous codebehind changes did not appear in the rendered pdf.
atljarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

atljarmanAuthor Commented:
I guess an easy way to say this in continuing to plug along is... is it possible to use DLookup for a column reference in a query.  That is really what I'm trying to achieve here.
0
PatHartmanCommented:
In an .accde/.accdr, you CANNOT do ANYTHING that requires putting an object in design view to change its properties.  Many properties can be changed at runtime such as color but you can not change the columns you select in a query.

You might have some success if you define an empty report shell with a bunch of unbound controls.  At runtime, you could build an SQL string using VBA to select some columns (up to the max number defined for the report).  in the Report's Open event, you can place the SQL string in the report's RecordSource.  Then - and here I am guessing since I have never done this nor would I, you can bind each control to a field from the RecordSource and place an appropriate value in the control's attached label.

The problems with this are many.  Starting with not having a good way to change control widths on the fly.

You can make a much more flexible tool if you output to Excel rather than trying to make an Access report.  You can give the users a basic query builder and then using TransferSpreadsheet, export the data they select to a spreadsheet.  At that point, the user could open the spreadsheet and pretty it up if he wants to present the report to someone.  You can do some of that yourself first.  For example, you could automate Excel to open the workbook you just created and select all the columns and make them size to fit.  You could also select the first row, which would be headers, and change the font or point size or color to make them stand out.

If your users are sophisticated enough and tech savvy enough to build their own reports, create an application shell for them.  Link to the tables they will need but hide all the tables.  Instead, you will create queries that do basic stuff like joining to lookup tables to get text values so the user doesn't have to deal with IDS.  You should also create a dummy, one-row table and include it in EVERY query.  Since you are not using an actual join, the query will technically create a Cartesian Product which will render it not updateable.  Then the users can use the QBE and the report writer to create their own reports.
0
atljarmanAuthor Commented:
I think I solved this issue.  I went back and rewrote the query to include the setting table labels in the original query.  Then I referenced the values of the settings in the column header.  It's working so far.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.