Solved

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

Posted on 2009-04-15
4
242 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
  • 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now