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
Solved

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

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Stored Proc - Performance Enhancement 15 55
ADODB problem 20 38
Error can't find table ?? 5 25
Accessing variables in MySQL query 4 31
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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

829 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