| Author |
Topic |
|
skythexmoliron
Starting Member
10 Posts |
 Posted - 2004-06-23 : 15:47:06
|
| I need assistance with creating a query. Here's my problem:The query needs to obtain data from a form, where the data is presented as a combo box. In this combo box are pre-set values. I want to search IN these values in a specific table such that the field in the query would substantially be Forms![Form Name]![Form Source]. Is there such a way to create this? I tried creating some sort of pass-by-reference system using parameters, but this failed. I've tried everything that I can think of without using DAO and the likes. I am primarily working with the access SQL, but can not figure out the protocol. If I doSELECT Forms!Form Name!Form SourceFROM Table Name...The form does not interpret the Forms!Form Name!Form Soure properly. It either tries that table, or if I try to make Access evaluate it by putting an equalsign in front of it, it does that as the search field. Currently, I have the search field asWHERE Forms!Form Name!Form Source = Forms!Form Name!Form Source2Allow me to reitterate what I'm trying to do.I'm trying to create a form that will allow me to use a query-based filter to associate it to user-input. Currently, I have the Criteria portion working, but cannot get Access to recognize the field. I have the tables separated into 3 different sources, where one would be the Filter information, and the other two would be sort-by-years. This tool is very important, as there are in excess of half a million records that I must sort through.I have also tried UNION and JOIN types, but to no avail. I would prefer not to have to use too much Visual Basic, as I will use this query more often than not.Also, any suggestions as per making the query query only the specific year that I select on the form would be helpful. I have the records separated so as to create a faster filter-base. I'm going to suppose it has something to do with an IIF() statement, but I'm not sure how I would write it. This I haven't experimented with, but any suggestions that will save me time in experimenting would be appreciated. |
|
|
skythexmoliron
Starting Member
10 Posts |
Posted - 2004-06-23 : 15:50:42
|
| "It either tries that table, or if I try to make Access evaluate it by putting an equalsign in front of it, it does that as the search field."It tries the actual string "Forms!Form Name!Form Source" as the criteria in the query. The Equal-sign makes the query criteria what I have stored in the combo box. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-23 : 16:17:15
|
| Please post your question in the Access forum.Tara |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-23 : 19:23:43
|
| As for your first problem - you'll need to build the query dynamically then execute it. That means having to use VB code. sql = "SELECT " & Forms!Form Name!Form Source & " FROM Table Name"You'll have to explain your second question a bit better. I'm not sure what you're asking... |
|
|
|
skythexmoliron
Starting Member
10 Posts |
Posted - 2004-06-24 : 07:07:26
|
| Thanks for the reply. I figured I would end up having to go to VB, but that's fine. As per the second question:I have two tables that are separated by year. Depending on the selection on the form, I would like to be able to open the respective spreadsheet.For example: I select 2004 on the form (meaning that I want to sort through the 2004 Table) Here's the code that I would imagine would be the most effective.Iff(Forms!Form Name!Year = 2004, *not sure what to do if false*, DoCmd.OpenTable "Table Name")I need to know how to properly format that if statement, or if that's the right way to go about the situation. Thanks Again |
|
|
|
skythexmoliron
Starting Member
10 Posts |
Posted - 2004-06-24 : 07:47:15
|
| Dim filterSQL As String Dim db As DAO.Database Dim qdf As DAO.QueryDef Set db = CurrentDb Set qdf = db.QueryDefs("FilterQuery") filterSQL = "SELECT " & Forms!Filter!Source & _ " FROM Fy0469all WHERE " & _ Forms!Filter!Source & " = " & Forms!Filter!Filter qdf.SQL = filterSQL DoCmd.OpenTable "Fy0469all" DoCmd.ApplyFilter "FilterQuery"Here's my current code, but when I try to run it, it says "Syntax error (missing operator) in expression '*whatever is stored in the source*' |
|
|
|
skythexmoliron
Starting Member
10 Posts |
Posted - 2004-06-24 : 09:00:29
|
| My mistake. I was able to solve the problem. I added brackets before the quotation end. Now I'm trying to figure out the second task, doesn't seem to be working. I wanted to create a group that I could goto, and it would run those tasks, and I would do without a false statement. Just two iff statements. But it doesn't seem to allow me to nest the goto statement within an iff statement. It seems to want another expression of some sort. I will continue to experiment, but any help would be, again, appreciated. Thanks |
|
|
|
skythexmoliron
Starting Member
10 Posts |
Posted - 2004-06-24 : 09:17:53
|
| Nevermind, I figured this out also. Thanks |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-24 : 19:39:50
|
| It's funny sometimes - The process of explaining the problem sometimes helps you to solve it yourself.... |
|
|
|
skythexmoliron
Starting Member
10 Posts |
Posted - 2004-06-28 : 06:50:02
|
| So true...so very, very true. Thanks for the help, though |
|
|
|
|
Query Assistance