Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-04-15
4
Medium Priority
?
277 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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…

715 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