Solved

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

Posted on 2009-04-15
4
262 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:pgerman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 

Author Comment

by:pgerman
ID: 24146485
Is there a way to do this through the Textbox's properties, for example setting the control source or default value?
0
 

Author Comment

by:pgerman
ID: 24146562
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
 

Author Comment

by:pgerman
ID: 24146620
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 24146644
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question