 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / March 2005Form --> Query
Hello, Is it possible to use a form to select which table a Query will utilize.? I have three different tables: 1)Sales 2)COS 3)Income. Each table has 3 columns/fields 1)contract 2)amount 3)Date. I need to run similar reports from each. I would like to run a report from any of the three reports and then select the date range to run from the report - using one form. The only way I know how to do this is to create 3 different queries, and then have the user select on the Form which query to run. Is it possible to have the user select on the form which table to use in one query? Thank You You could, but if the field names are the same, why not just base the single report on a single query and modify the SQL when you click the "Launch Report" button > Hello, > [quoted text clipped - 14 lines] > > Thank You Thank You for your response. Could you describe how I can modify the SQL to specify which table to use when I click the "Launch" command. I am not sure how to go about doing this. If I need to use VBA code, could you please give an example of the code. Also, the amount field title is different in each table one for Sales, one for COS and one for Income. Thank You, > You could, but if the field names are the same, why not just base the single > report on a single query and modify the SQL when you click the "Launch [quoted text clipped - 18 lines] > > > > Thank You I think your best bet is 3 separate queries / reports if you are unfamilliar with code and the fields are different. > Thank You for your response. > Could you describe how I can modify the SQL to specify which table to use [quoted text clipped - 34 lines] >> > >> > Thank You Can you refer me to somewhere that I can look into changing the table name in an SQL query with VBA code. I am familiar (to some extent, about 4 yr experience) with using VBA code. I have just never changed SQL code with variables. I think setting up or declaring the variables to change the table and field names is giving me the most trouble. Setting up to change the table name is my biggest problem, I think. I believe I can work around the field names. Thank You > I think your best bet is 3 separate queries / reports if you are unfamilliar > with code and the fields are different. [quoted text clipped - 37 lines] > >> > > >> > Thank You CurrentDb.QueryDefs("qryTest").SQL= Replace(CurrentDb.QueryDefs("qryTest").SQL,"tblTest1","tblTest2")  SignatureBrendan Reynolds (MVP) > Can you refer me to somewhere that I can look into changing the table name > in an SQL query with VBA code. I am familiar (to some extent, about 4 yr [quoted text clipped - 56 lines] >> >> > >> >> > Thank You Thank You, This looks like what I need. How can I determine the name of the first table "tblTest1" --the name of the table currently used in the query. This will be the only table in the query. Do you know of the code to determine the table/tables currently used in the Query. I think that once this is determined I can declare it as "tblTest1" and the query will change as you show below. Thank You, > CurrentDb.QueryDefs("qryTest").SQL= > Replace(CurrentDb.QueryDefs("qryTest").SQL,"tblTest1","tblTest2") [quoted text clipped - 59 lines] > >> >> > > >> >> > Thank You That could get decidedly tricky. I suppose if you're sure that the query includes only one table you could search the SQL for the text ' FROM ' and then assume that everything between that text and the next space is the name of the table - unless you have tables with spaces in their names, when you'll have to search for the "[" and "]" characters following the ' FROM ' text. Keep in mind that there's nothing preventing anyone from having a field in a table with the same name as the table itself, or from including the name as part of a field name, so even after you've found the name of the table, you can't go blindly replacing it anywhere it happens to appear in the SQL. This is a decidedly non-trivial parsing operation you've taken on here. I can't help wondering why it would ever be necessary - if you don't know what the name of the table is, how do you know it needs to be changed, let alone to what it should be changed? SignatureBrendan Reynolds (MVP) > Thank You, > This looks like what I need. [quoted text clipped - 85 lines] >> >> >> > >> >> >> > Thank You I may be going about this in the wrong way. I have several tables with three fields in them Contract, Amount, Date. The amounts of each table represent the values of different types of accounts. I am trying to use a form to allow my users to select which report they would like to run. A combo box containes each tables name. I only want to have to use one query. This is why my vba code would need to change the SQL code within the specified query. Since the user would be running various reports, the name of the table it is referencing would change each time the user runs a new report. This is why you would not know the name of the table that the query is currently using, and would need the vba script to change it to your desired tablename (the name selected in the forms combobox). The purpose of this is to essentially create one query which can maneuver to the users needs rather than creating numerous additional queries for this single userform. > That could get decidedly tricky. I suppose if you're sure that the query > includes only one table you could search the SQL for the text ' FROM ' and [quoted text clipped - 102 lines] > >> >> >> > > >> >> >> > Thank You From the code below: My problem is occurring between the FROM and the WHERE clause. Any suggestions? Thank you, qryTest.SQL = "SELECT [Profit Center], [Amount], [Date] FROM ([Forms]![Inv Rel]![AC1AC]) WHERE (((Date)=[forms]![Inv Rel]![AC1YR]& [forms]![Inv Rel]![AC1MT]));" > I may be going about this in the wrong way. > I have several tables with three fields in them Contract, Amount, Date. [quoted text clipped - 117 lines] > > >> >> >> > > > >> >> >> > Thank You The problem is a flawed database design. There should be only one table. SignatureBrendan Reynolds (MVP) >I may be going about this in the wrong way. > I have several tables with three fields in them Contract, Amount, Date. [quoted text clipped - 144 lines] >> >> >> >> > >> >> >> >> > Thank You |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.