Event Procedure code needed

[dakota1606]

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

[Echidna]

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]

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.

[dakota1606]

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

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

[dakota1606]

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.

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):
[code=sql]
(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.CreateQueryD ef("Dynamic_Que ry", "Select * from Depreciation " & (" where " + Mid(where, 6) & ";"))[/code]

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]

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.

[dakota1606]

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

[nico5038]

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]

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.

[dakota1606]

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.

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!

[dakota1606]

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!

[nico5038]

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)

[dakota1606]

Thanks for your input. I appreciate it.

Dakota1606

[NeoPa]

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.

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.

[dakota1606]

That's just what I need. Thank you very much!

dakota1606