If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query Inside an SQL statement
Hi and TIA. What I'm wondering is can you use aquery name in place of a
table name when building an sql statement in code? Example below can I replace MyTable with MyQuery. It bombs out on me when I run the code. My goal is to creat several Union queries in my db. Have the user select tables on my form to use in the results and depending on what they select I will use that saved Union query in place of the table name (MyTable) below.Thanks for your time. Set db = CurrentDb() Set qdf = db.QueryDefs("qryMultiSelect") If Me!lstUIC_ID.ItemsSelected.Count 0 Then For Each varItem In Me!lstUIC_ID.ItemsSelected strCriteria = strCriteria & "MyTable.UIC = " & Chr(34) _ & Me!lstUIC_ID.ItemData(varItem) & Chr(34) & "OR " Next varItem strCriteria = Left(strCriteria, Len(strCriteria) - 3) Else strCriteria = "MyTable.UIC Like '*'" End If strSQL = "SELECT * FROM MyTable" & _ "WHERE " & strCriteria & ";" qdf.SQL = strSQL DoCmd.OpenQuery "9h1_qryMultiSelect" -- Reggie |
#2
|
|||
|
|||
Query Inside an SQL statement
hi,
On 04.05.2010 09:21, Mr. Bud wrote: Hi and TIA. What I'm wondering is can you use aquery name in place of a table name when building an sql statement in code? You can. Example below can I replace MyTable with MyQuery. It bombs out on me when I run the code. hmm, "bombs out" is not a quite accurate error description. Set db = CurrentDb() Set qdf = db.QueryDefs("qryMultiSelect") If Me!lstUIC_ID.ItemsSelected.Count 0 Then For Each varItem In Me!lstUIC_ID.ItemsSelected strCriteria = strCriteria & "MyTable.UIC = " & Chr(34) _ & Me!lstUIC_ID.ItemData(varItem) & Chr(34) & "OR " Next varItem strCriteria = Left(strCriteria, Len(strCriteria) - 3) Else strCriteria = "MyTable.UIC Like '*'" End If strSQL = "SELECT * FROM MyTable" & _ "WHERE " & strCriteria & ";" qdf.SQL = strSQL Use Debug.Ptin strSQL to output the SQL statement to the immediate window. Copy'n'paste it into a new query. Try to fix it. Then adjust your code... mfG -- stefan -- |
#3
|
|||
|
|||
Query Inside an SQL statement
Mr. Bud wrote:
Hi and TIA. What I'm wondering is can you use aquery name in place of a table name when building an sql statement in code? Example below can I replace MyTable with MyQuery. Yes. A saved query looks like a table to the query engine. It bombs out on me when I run the code. :-) We're not looking over your shoulder at your screen. What is the error message you get? My goal is to creat several Union queries in my db. ? Why several union queries? Or do you mean to say you have created a union query that combines the data from several tables? Have the user select tables on my form to use in the results and depending on what they select I will use that saved Union query in place of the table name (MyTable) below.Thanks for your time. Set db = CurrentDb() Set qdf = db.QueryDefs("qryMultiSelect") If Me!lstUIC_ID.ItemsSelected.Count 0 Then For Each varItem In Me!lstUIC_ID.ItemsSelected strCriteria = strCriteria & "MyTable.UIC = " & Chr(34) _ & Me!lstUIC_ID.ItemData(varItem) & Chr(34) & "OR " Next varItem strCriteria = Left(strCriteria, Len(strCriteria) - 3) Else strCriteria = "MyTable.UIC Like '*'" End If strSQL = "SELECT * FROM MyTable" & _ "WHERE " & strCriteria & ";" qdf.SQL = strSQL This sql statement will raise a syntax error because you have failed to put a space between MyTable and WHERE. While debugging, you should look at the results of your concatenation. You can do that by using msgbox or debug.print. debug.print strSQL DoCmd.OpenQuery "9h1_qryMultiSelect" Do you have a saved query called "9h1_qryMultiSelect"? if not, this last line will raise an "object not found" error. Does qryMultiSelect contain your union query? And does that union query return a field called UIC? If so, your code is replacing that union query with a simple select statement. Or is there another saved query that does the union? And qryMultiSelect is intended just to be used in your OpenQuery statement? If so, then you should be able to use the name of that saved union query (whatever its name is) in your sql. Like this: strSQL = "SELECT * FROM qryUnion " & _ "WHERE " & strCriteria & ";" qdf.SQL = strSQL DoCmd.OpenQuery "qryMultiSelect" My preference is not to display the results of a query in a query window. Given that this is read-only data, I would create a report whose data source is qryMultiSelect and use DoCmd.OpenReport instead. Alternatively, you can use a subform whose form's data source is qryMultiSelect and use subformname.Requery -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#4
|
|||
|
|||
Query Inside an SQL statement
Responses below:
Thanks All for your responses and sorry for my lame post. I figured out the problem it was because the query name I am using has a leading number in the name. Once I put the name in brackets it runs like a charm. Guess the number was throwing off the sql statement. "Bob Barrows" wrote in message ... Mr. Bud wrote: Hi and TIA. What I'm wondering is can you use aquery name in place of a table name when building an sql statement in code? Example below can I replace MyTable with MyQuery. Yes. A saved query looks like a table to the query engine. It bombs out on me when I run the code. :-) We're not looking over your shoulder at your screen. What is the error message you get? My goal is to creat several Union queries in my db. ? Why several union queries? Or do you mean to say you have created a union query that combines the data from several tables? Have the user select tables on my form to use in the results and depending on what they select I will use that saved Union query in place of the table name (MyTable) below.Thanks for your time. Set db = CurrentDb() Set qdf = db.QueryDefs("qryMultiSelect") If Me!lstUIC_ID.ItemsSelected.Count 0 Then For Each varItem In Me!lstUIC_ID.ItemsSelected strCriteria = strCriteria & "MyTable.UIC = " & Chr(34) _ & Me!lstUIC_ID.ItemData(varItem) & Chr(34) & "OR " Next varItem strCriteria = Left(strCriteria, Len(strCriteria) - 3) Else strCriteria = "MyTable.UIC Like '*'" End If strSQL = "SELECT * FROM MyTable" & _ "WHERE " & strCriteria & ";" qdf.SQL = strSQL This sql statement will raise a syntax error because you have failed to put a space between MyTable and WHERE. While debugging, you should look at the results of your concatenation. You can do that by using msgbox or debug.print. debug.print strSQL DoCmd.OpenQuery "9h1_qryMultiSelect" Do you have a saved query called "9h1_qryMultiSelect"? if not, this last line will raise an "object not found" error. Does qryMultiSelect contain your union query? And does that union query return a field called UIC? If so, your code is replacing that union query with a simple select statement. Or is there another saved query that does the union? And qryMultiSelect is intended just to be used in your OpenQuery statement? If so, then you should be able to use the name of that saved union query (whatever its name is) in your sql. Like this: This is correct. Using my list box on the form the user wil select a table or a combination of the tables (only 3 off them) Depending On their selection I will pass a string (strQueryName) to this procedure containing the name of the predefined union query and use in place of qryUnion you have below. The qryMultiSelect is just a holder query and built on the fly strSQL = "SELECT * FROM qryUnion " & _ "WHERE " & strCriteria & ";" qdf.SQL = strSQL DoCmd.OpenQuery "qryMultiSelect" My preference is not to display the results of a query in a query window. Given that this is read-only data, I would create a report whose data source is qryMultiSelect and use DoCmd.OpenReport instead. Alternatively, you can use a subform whose form's data source is qryMultiSelect and use subformname.Requery Yep. I'm simply exporting the results to excel. -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" -- Reggie |
Thread Tools | |
Display Modes | |
|
|