I feed dropdown list information from a form to this code to create and execute a dynamic_query in Access MS Office 2000. All this works fine but I need additional code (wording and format) to be able to add GROUP BY and ORDER BY information (provided from the form) to the query. Can anybody supply the wording in this code set for me to be able to do this?
This is the sample code. - Private Sub SetCriteria_Click()
-
On Error GoTo Err_SetCriteria_Click
-
-
-
'This is the Search Engine routine.
-
-
Dim db As Database
-
Dim QD As QueryDef
-
Dim where As Variant
-
-
Set db = DBEngine.Workspaces(0).Databases(0)
-
-
'Delete existing dynamic query, trap error if it does not exist.
-
On Error Resume Next
-
db.QueryDefs.Delete ("Dynamic_Query")
-
On Error GoTo 0
-
-
'Note Single quotes surrounding text fields [Territory]
-
'Note NO Single quotes surrounding any Numeric field [terr]
-
'This section sets the criteria for the dynamic_query
-
where = Null
-
where = where & (" AND [connum]")
-
where = where & (" AND [ACCT]= '" + Me![Account] + "'")
-
where = where & (" AND [CATEGORY]= '" + Me![CATEGORY] + "'")
-
where = where & (" AND [Expires]= '" + Me![Expire Date] + "'")
-
-
'MsgBox "Select * from contract " & (" where " + Mid(where, 6) & ";")
-
'Remove previous line to NOT have the SQL statement displayed
-
-
Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from Depreciation " & (" where " + Mid(where, 6) & ";"))
-
-
DoCmd.ApplyFilter "Dynamic_Query"
-
-
Dim stDocName As String
-
stDocName = "Dynamic_Query"
-
DoCmd.OpenQuery stDocName, acNormal, acEdit
-
-
Exit_SetCriteria_Click:
-
Exit Sub
-
-
Err_SetCriteria_Click:
-
MsgBox Err.DESCRIPTION
-
Resume Exit_SetCriteria_Click
-
-
End Sub
15 3129
Hi,
Group by is used after the Where clause in the select statement.
Group By [Column Name Reference] Having [Criteria]
Order by appears at the end of the statement with the columns appearing in sequence of order.
Order by [Column Name Ref 1] (ascending), [Column Name Ref 2] Desc (Descending)
Hope this helps
Leon
NeoPa 32,556
Expert Mod 16PB
This all seems very convoluted and long-wnded if what you're after is simply (in the 1st instance) to filter your running form, and as an addendum, apply GROUP BY and ORDER BY control to your running form.
Unfortunately, you're not very clear exactly what you want or need. Do you already have the basics of the GROUP BY set up in the original data source? To answer most helpfully we need this info.
In its absence for now, I would say that filtering is more easily done by setting the Me.Filter property to the SQL WHERE string you have created but without the word WHERE. You must also set Me.FilterOn to True if it's not already so set.
Similar is true for ordering using the properties OrderBy & OrderByOn.
@Echidna
I appreciate your response. Unfortunately, it doesn't take me to the next step. I'm a novice. I have a book on SQL programming and I can find the syntax when I need it. In this case, I need help with the exact wording and marking in my code. Here is a sample (refer to my posted SQL code): -
(The "where" I have and it works)
-
where = where & (" AND [ACCT]= '" + Me![Account] + "'")
-
-
(The "Order By" and "Group By" I don't have. I tried to write it in but I can't get past msg End of Statement Expected)
-
Group By = Null
-
Group By = Group By & (" AND [ACCT]= '" + Me![account] + "'")
-
-
Order By = Null
-
Order By = Order By & (" AND [PURCH COST]= '" + Me![cost] + "'")
-
-
Once I get the above two criteria set and to compile correctly, I need help writing (modifing) the Set Qd statement which is this:
-
-
Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from Depreciation " & (" where " + Mid(where, 6) & ";"))
I use MS Access in my self-employed business. I'm not a code writer, I'm just trying make work what I have.
Again, thanks!
dakota1606
@NeoPa
I know it's crude. I'm not a programmer.
I appreciate your response. Unfortunately, it doesn't take me to the next step. I'm a novice. I have a book on SQL programming and I can find the syntax when I need it. In this case, I need help with the exact wording and marking in my code. Here is a sample (refer to my posted SQL code): -
(The "where" I have and it works)
-
where = where & (" AND [ACCT]= '" + Me![Account] + "'")
-
-
(The "Order By" and "Group By" I don't have. I tried to write it in but I can't get past msg End of Statement Expected)
-
Group By = Null
-
Group By = Group By & (" AND [ACCT]= '" + Me![account] + "'")
-
-
Order By = Null
-
Order By = Order By & (" AND [PURCH COST]= '" + Me![cost] + "'")
-
-
Once I get the above two criteria set and to compile correctly, I need help writing (modifing) the Set Qd statement which is this:
-
-
Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from Depreciation " & (" where " + Mid(where, 6) & ";"))
I use MS Access in my self-employed business. I'm not a code writer, I'm just trying make work what I have.
Again, thanks!
dakota1606
NeoPa 32,556
Expert Mod 16PB
If I find somebody on the wrong road, I don't tell him the next step. I tell him he's on the wrong road, and which road to take.
Now, it's hard for me to take you anywhere as you don't explain what you're trying to do very clearly. I can only help as far as you allow me to and with whatever information I have. In this case very little, which doesn't make much sense. Normally we try to read between the lines, but the information seems to be contradictory (at least whichever way I guess you're heading makes no sense) and I don't feel it would be productive for me to try to 2nd guess you.
I'm here available, but you need to get involved and respond a little more positively with information if you're to get any benefit from that.
I will try one more time. Sorry if I didn’t get my code tagged right. I read the Help on Code Tagging and I thought I had it right. Not a programmer, remember?
Code=SQL (I interpreted as Starts Here)
/SQL (Ends Here)
In MS Access I use a form which is related to my database. On the form I have a Combo Box labeled Account and it’s drop down list is populated with the seven account numbers from the ACCT column in my table. So that I don’t have so many different querys to choose from, I have begun using a dynamic_query which I found in a sample data base that came with my Office 2000 application. On the form, I also have a Command Button which, On_click, runs an Event Procedure (which is the object of my mission here). The Combo Box label is Set by the Procedure code as Me![Account] (see below).
SQL - 'This section sets the criteria for the dynamic_query
-
where = Null
-
where = where & (" AND [ACCT]= '" + Me![Account] + "'")
When I wish to do a search, for all records by ACCT number, I click the drop down on the Account Combo Box, select one of the seven account numbers on my list (ie. 1620) and it populates the Box. When I click the Command Button (labeled Search), the Event Procedure runs and first deletes the current copy of Dynamic_Query. Then it plugs the Me![Account] variable into the Environment as ACCT.1620 and creates a new Dynamic_Query using this line of code: - Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from Depreciation " & (" where " + Mid(where, 6) & ";"))
-
-
followed by:
-
DoCmd.ApplyFilter "Dynamic_Query"
The resultant SQL statement in the new query is: - SELECT *
-
FROM Depreciation
-
WHERE [ACCT]= '1620';
On my form I have a second Combo Box labeled FieldName which is populated with of all the column names from my table (DEPRECIATION) which I want to use to provide the Column name for Order By in my query.
I have tried to create the criteria as below but won’t pass Compile. - 'This section sets the criteria for the dynamic_query
-
Order By = Null
-
Order By = Order By & (" AND [PURCH COST]= '" + Me![FieldName] + "'")
When I write it as below, it does pass Compile and it moves on to Set QD - Order_By = Null
-
Order_By = Order_By & (" AND [PURCH COST]= '" + Me![FieldName] + "'")
I have tried to write the Set QD string a number of different ways as: - Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from Depreciation " & (" where " + Mid(where, 6)) & (" Order By + Mid(Order By, 6)) & ;"))
-
-
Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from Depreciation " & (" where " + Mid(where, 6)) & (" Order_By " + Mid(Order_By, 6) & ";"))
but can’t get it to Compile. I get Expected messages or Syntax messages and have been unable to figure it all out.
If this is too “time wasting” simple for you, please let me know and I will go elsewhere.
dakota1606
Hi dakota1606,
Just puzzled why you are rebuilding the Access query editor.
Can you explain what problem you're trying to solve with this "dynamic query"?
As far as I can see the Group By has no effect, as there's no aggregation function (Like Sum(), Max(), Min(), etc) specified for the selected fields. For just removing dupes a simple "SELECT DISTINCT" will work as well...
Nic;o)
NeoPa 32,556
Expert Mod 16PB
Dakota,
I'm not trying to say this is too simple to answer. Far from it. I'm merely trying to lead you to a better understanding of what you're doing.
I was hoping to get you to see where this code is not serving you well by encouraging you to put into words exactly what you're looking for it to do. I can see that you have attempted to do this and so I will proceed, regardless of the fact that I think you still haven't grasped the issue clearly here. (Excuse me. I'm not trying to be rude. Simply to explain as clearly as I can.)
The SQL you create to filter by is fine. I was trying to indicate that a QueryDef need not be created to apply this filter to the form. There is a more straightforward way which is simply to pass the (already well formulated) SQL string to the .FilterBy property, and set the .FilterByOn property to True. No requirement for the time-consuming process of creating (& deleting & generally managing) a QueryDef object.
As for ORDER BY and GROUP By clauses, these are of no effect in a filter. These could only make sense in the .RecordSource property of the form. Hence again, my earlier request for consideration & clarification of your requirement. I still find myself unsure as to your intent here. Many times the design of the query that the form is based on is adequate to control these factors. When not, the .RecordSource itself needs to be changed in the VBA code. As I say - we can help if the requirement is made clear.
As for your current code - you seem to be treating the SQL reserved words (ORDER BY & GROUP BY) as if they were variables in your code. Trying to assign values to them. This cannot work as it makes no sense. Even if you did something similar with valid variables it would not help unless you applied it to the .RecordSource property somehow.
Lastly, the tags are much easier to use when they're understood. I will dig up a post I made earlier that explains more helpfully : Tags (generally) are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [code] tags has a hash (#) on it. You can choose which editor to use in your Profile Options (Look near the bottom of the page). You must select the code in your post, and hit the button on the tool bar which looks like a hash (#). This will automatically format the post such that the [code] tags surround the code you're posting. This will display the code in a Code Box, quite differently from the standard formatting of a post. PS. Nico makes a good point about the use of the SQL DISTINCT predicate and the GROUP BY clause. If this is your issue that is an easier way to go.
@NeoPa
Thanks for your reply. I will try to do more studying on the fundementals. Can you direct me to a source that could help me understand how to use characters like ' , " () [] and spaces or no spaces and what they mean. I'm trying to get a better handle on how to use Event Procedures in Access to my advantage.
In this case I am just trying to make it easier for my operator to select query critera to pull togather reports by using drop downs and pushing buttons.
It's a mission.
Thanks!
I'm not trying to solve any problems. I'm trying to make it easier for my operator to pull togather criteria to generate a report.
Thanks!
I would in that case probably instruct the operator how to use the Access graphical query editor, as that will allow every necessary report.
When you just need a filtering of records, than you can check out the attached demo that's using the defined filter by the right-click popup and allows the user to store a filter by name.
Nic;o)
Thanks for your input. I appreciate it.
Dakota1606
NeoPa 32,556
Expert Mod 16PB @dakota1606
Try Quotes (') and Double-Quotes (") - Where and When to use them for some of that.
Parentheses () are simply used to indicate items to be processed first. I don't know of any special purpose otherwise.
Brackets [] can be used anywhere to delineate object names. They are necessary when those names do not make clear sense in their absence. EG. When the name contains embedded spaces or where it clashes with reserved words.
That's just what I need. Thank you very much!
dakota1606
NeoPa 32,556
Expert Mod 16PB Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mike |
last post by:
How do I prevent SQL Server 2000 from posting successful backup
completion messages to the Windows 2000 Application Event Log?
I have scheduled jobs which backup my transaction logs on 50+...
|
by: my-wings |
last post by:
I think I've painted myself into a corner, and I'm hoping someone can help
me out.
I have a table of books (tblBooks), which includes a field (strPubName) for
Publisher Name and another field...
|
by: R Millman |
last post by:
under ASP.NET, single stepping in debug mode appears not
to stop within event procedures. i.e. 1) Create web page
with submit button and event procedure for the click
event in the code behind...
|
by: Jordan |
last post by:
I need to dynamically add an ImageButton control to a user control and and
do some server-side processing when the user clicks it. While I the
ImageButton is added to the user control at runtime,...
|
by: Verde |
last post by:
This is admittedly an apparently odd request... but please indulge me if you
don't mind:
Suppose I have two <asp:Button.../> on a page (Button1 and Button2). User
clicks Button1 and triggers a...
|
by: Jack Russell |
last post by:
My unstanding of all VB up to and including vb6 is that an event could
not "interrupt" itself.
For instance if you had a timer event containing a msgbox then you would
only get one message.
...
|
by: Jerry |
last post by:
Hi,
My app is controled by a treeview.
Each node brings a subform for input and calculations to the front. The
subforms are loaded as controls on the main form.
Dim ctl As Control
For Each...
|
by: serge |
last post by:
/*
Subject: How to build a procedure that returns different
numbers of columns as a result based on a parameter.
You can copy/paste this whole post in SQL Query Analyzer
or Management Studio...
|
by: Neil |
last post by:
I am using Access 2003, and have an ongoing problem of every once in a while
losing an event procedure
for a form or control. The procedure's still in the code module, but the
form or control's...
|
by: =?Utf-8?B?SGV6YWw=?= |
last post by:
Hi,
I am trying to add a new record to a table but everytime I click the button,
somehow it saves the record twice...
I've created a stored procedure to insert records into a table and I called...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |