How to select fields for a MS Access Query based on the entry to a text box?

I am building a query that I would like to run every month for the latest data, without having to go into the back end (design or SQL view) of the query.  My db uses a main entry form and requires the user to enter a four digit date, for example 0209 for February 2009.

My main table that the query is based on has fields labeled with the four digit time codes dating back to 2007 and running through 2009.  Each month a new column of data is added. So for example right now, the user would enter 0409.  I want the query to pull in the field 0409.

I have tried this:
SELECT TIME_CODE_REFERENCE_TABLE.fldTIME_CODE, Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings, Sum([SumEarnings2009].[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]) AS Expr1
FROM (TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate) INNER JOIN masterdata ON SumEarnings2009.GroupAccount = masterdata.[CAS ACCT NUMBER]
GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE
HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));

or just putting Expr1: Sum([SumEarnings2009].[& Time_Code_txt &]) into the Field section in Design view.

Both don't work.
pgermanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
So you're adding a new COLUMN of data for each month? This is your true issue - you should never add columns to tables at runtime; your db design should be set before your users ever even see the application. Instead, you should store your data in a related table, with a column to indicate the Date of that data, and other columns to store the values as needed.

Even if you already HAVE columns for each month, your database isn't properly designed. Access is not a spreadsheet, and it appears you've built you application around that concept. Access stores data in "rows", not "columns" ... what happens, for example, at the end of 2009? You'd have to revisit the database design, add more columns, etc etc ... also, Access has a 255 column limit which you would of course run into at some point.

That said, you could always directly manipulate the SQL of your query:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDB.QueryDefs("YourQuery")

qdf.SQL = "SELECT TIME_CODE_REFERENCE_TABLE.fldTIME_CODE, Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings, Sum([SumEarnings2009]." & [Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt] & ") AS Expr1 FROM (TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate) INNER JOIN masterdata ON SumEarnings2009.GroupAccount = masterdata.[CAS ACCT NUMBER] GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=" & [Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt] & "));"

This will dynamically build the SQL for that query.

However, you would be well advised to straighten out your database design before getting much further along. Troubles like this can cause no end of issues - for example, how will you build a report that will take into account the new columns you must add? Granted you can do something similar - that is, open the report in Design view, change the ControlSource of your textboxes, etc - but that is a major kludge, and will decompile your application (and make it much less stable and more prone to corruption). And, as you go along, you'll find seemingly simple tasks become very difficult. How will you compare data from different months? What happens when you need to show all of xxx for two or more months, for various rows? With a normalized structure, it's pretty simple; with your structure, you'd almost have to resort to temporary tables and such ...
0
 
pgermanAuthor Commented:
Is there a way to do this through the Textbox's properties, for example setting the control source or default value?
0
 
pgermanAuthor Commented:
I also tried something like this in the field section of design view:

Expr1: Sum([SumEarnings2009].[Choose(Val([Forms]![GEM to CMR ENTER DIFFERENCES ORM]![Time_Code_txt]),masterdata.0107,masterdata.0207,masterdata.0307,masterdata.0407,masterdata.0507,masterdata.0607,masterdata.0707,masterdata.0807,masterdata.0907,masterdata.1007,masterdata.1107,masterdata.1207,masterdata.0108,masterdata.0208,masterdata.0308,masterdata.0408,masterdata.0508,masterdata.0608,masterdata.0708,masterdata.0808,masterdata.0908])
0
 
pgermanAuthor Commented:
Also tried this:

SELECT TIME_CODE_REFERENCE_TABLE.fldTIME_CODE, Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings, Sum((masterdata.[" & Time_Code_txt & "])) AS TOPFileCurrentPeriodBankEarnings
FROM (TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE=SumEarnings2009.CycleDate) INNER JOIN masterdata ON SumEarnings2009.GroupAccount=masterdata.[CAS ACCT NUMBER]
GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE
HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=Forms![GEM to CMR ENTER DIFFERENCES FORM]!Time_Code_txt));
0
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.

All Courses

From novice to tech pro — start learning today.