Forum Discussion
Run a Query without Saving it (to reduce # objects)
Hello Experts,
I want to reduce the # of objects I have in my db.
One way I can do this is by not saving queries and running the select statement from a button on my form with code instead.
I have read some posts and not exactly sure how best to do this.
I think its with StrSQL statement and DoCmd.RunSQL strSQL but not exactly sure.
I do run INSERT queries this way but not exactly sure if I can run SELECT queries and if so then how/where to make the changes.
My query is as follows:
SELECT import_Excel_BOA.[As of Date], import_Excel_BOA.Amount, import_Excel_BOA.[Account Number], import_Excel_BOA.[Account Name], import_Excel_BOA.Text
FROM import_Excel_BOA
WHERE (((import_Excel_BOA.[Account Number]) Like "*" & [Enter 5213 for VW1 or 5636 for VWMC] & "*") AND ((import_Excel_BOA.Text) Like "*" & [enter part of co name] & "*"));
Please let me know if not clear.
thank you for the help!
Okay, so "Like" works, as would other approaches, when working with embedded digits in a larger number.
Yes, I missed out on removing the left square bracket.
To return to the SQL Syntax then ExcelBOA.[Account Number] is a number, not a string of digits in a text field.
Private Sub cmButtonNameGoesHere_Click() Dim strSQL As String strSQL = "SELECT import_Excel_BOA.[As of Date], import_Excel_BOA.Amount," & _ " import_Excel_BOA.[Account Number], " & _ " import_Excel_BOA.[Account Name], import_Excel_BOA.Text " & _ " FROM import_Excel_BOA " & _ " WHERE (import_Excel_BOA.[Account Number] Like ""*"" & [Enter 5213 for VW1 or 5636 for VWMC] & ""*"")" & _ " AND (import_Excel_BOA.Text Like ""*"" & [enter part of co name] & ""*"") ;" Me.ListBoxNameGoesHere.RowSource = strSQL End SubThat's as close as I can get working without data to validate against. I think I got all of the open and close parentheses in the right places, the square brackets where they are needed, and the quotes and double quotes in the right places and numbers.
8 Replies
Frankly, the goal of reducing objects may not be the best place to invest efforts. But that's your prerogative.
It depends on what you want to do with the recordset generated by the dynamic SQL. The primary places you could use them would be as the recordsource for a form or subform, or as the rowsource for a listbox or combobox. I suppose you could open one directly for a user to review, but that's not a common method.
Basically, you'd do this by rewriting the SELECT statement in VBA, modifying the WHERE clause in the process. Then you would assign that resulting SQL to the recordsource property of a form, or the rowsource of a control.
I don't have any recent examples of doing that, but it might be along these lines:
Private Sub cmButtonNameGoesHere_Click() Dim strSQL As String strSQL = "SELECT import_Excel_BOA.[As of Date], import_Excel_BOA.Amount, import_Excel_BOA.[Account Number], " & _ " import_Excel_BOA.[Account Name], import_Excel_BOA.Text " & _ " FROM import_Excel_BOA " & _ " WHERE (((import_Excel_BOA.[Account Number]) Like ""*"" & [Enter 5213 for VW1 or 5636 for VWMC] & ""*"") " & _ " AND ((import_Excel_BOA.Text) Like ""*"" & [enter part of co name] & """*"));" Me.ListBoxNameGoesHere.RowSource = strSQL End SubHI George, I added a listbox but I am getting the folllowing error. Let me know what the next step is when you have a sec.
One of the values in the where clause is wrong. I copied the SQL posted, without validation.
My best guess is the Account Number is the problem. You used Like so I copied that, but it's more likely that the Account Number is a number, and you need to use = not Like with numbers.
Try this for the line in the original
" WHERE import_ExcelBOA.[Account Number = " & [Enter 5213 for VW1 or 5636 for VWMC " &On the other hand, it's possible I guess, that 5213 and 5636 are part of a larger string of digits. In that case, we'd need to know that detail to know how to tweak the parameter.
Hi George, thanks for the reply. I now understand this is not a common thing to do. I might change my approach and save the query but woudl still like to have a workable solution.
To answer your question, i am simply wanting to show the query as a stand alone query. Not in a form...just the dataset as would show as if running a saved query from a form. It is not a subform.
Would I still need a ListBox? I didnt envision using a listbox. thank you.
No, if you just want to open the query for the user to look at, you can use DoCmd.RunSQL strSQL
