SQL way to specify multi-column index name as the ORDER BY?

[mdlueck]

I have created a named index which has multiple columns in said index.

I am trying via SQL to specify that index name as the ORDER BY object in a SQL statement. Access 2007 does not like such. This is a query against a FE temp table, so no other DB's involved than Access 2007.

Is there any syntax by which I can sort based on that index by referring to the index name and not have to specify the same column structure in the ORDER BY clause that is already defined in the index? After all, what would the point be to name indexes if you can not specify them by the same name?!

Paradox for Windows / Object PAL had such support... I could switch indexes to a known name index, and what ever the index had defined, the table would end up sorted thusly.

In this case I happen to be updating on-the-fly the SQL statement in a Form based on a Query to change the record display sort order. It works perfectly for sorting by single columns... but now I need to update the ORDER BY setting it to multiple columns, so looking for a way to ORDER BY indexname and what ever the index has defined will be the sort.

Suggestions of how to accomplish such? TIA!

P.S. Access 2007 is the version.

 

[bparkinson]

Nope, not how it works. Just add an ORDER BY clause that specifies the columns you wish to use to your SQL statement. Presumeably the same columns as you have indexed. The order of the columns in your ORDER BY clause will affect the output.

 

[mdlueck]

The complication is that this time, and I think only this time in the entire application, it would be nice to sort by multiple columns. The way I coded my sort capabilities, several things are keyed based on a single column name, such as not only the column name, but names of controls and what not.

If I was not choosing the SQL query method would it then be possible to specify an index name to sort by?

I guess I miss Paradox's "switch index" API... hhhhmmmm....

 

[bparkinson]

I'm an ANSI 92 SQL guy. Access may have some oddity that allows you to do this. I don't know of it.

It's really not that hard, in fact easier than replying to this thread time-wise, to just add the ORDER BY clause to your SQL statement.

You can have multiple columns in your ORDER BY clause, so you CAN sort by multiple columns.

I don't understand why this is a problem.

 

[mdlueck]

to just add the ORDER BY clause to your SQL statement.

You can have multiple columns in your ORDER BY clause, so you CAN sort by multiple columns.

I don't understand why this is a problem.

I can NOT have controls named with spaces in them. The "name" to sort by serves many purposes... the column name, part of the control name, etc... so sorting by multiple columns is possible, but it tosses a wrench into the shared common code which will then go off and try to arrive at a control name with a space in it.

 

[bparkinson]

If something as commonly done as adding an ORDER BY Clause to a SQL statement "tosses a wrench" into code, I would declare that code overly brittle, scrap it, and rewrite. We order datasets using SQL all the time in database development, and in a well architected app, this causes no trouble.

If your data layer relies on control names in the presentation layer, things might be a bit awry. It sounds like your application architecture is hamstringing you.

 

[mdlueck]

If something as commonly done as adding an ORDER BY Clause to a SQL statement "tosses a wrench" into code

You are not understanding...

I do have ORDER BY in the code.

The ORDER BY logic expects a single word to be what is should ORDER BY. That name is the column name, which I have the indexes (single column) match the column name. Also part of the control specific to a column has the same name.

The wrench comes with wanting to specify MULTIPLE columns in the ORDER BY clause.

Would be nice if Access would allow ORDER BY indexname and said index would sort by multiple columns.

Better?

 

[bparkinson]

As I said, if specifying multiple columns in a SQL ORDER BY clause breaks your code, your code is too brittle. Throw it out. Write code that doesn't choke on common SQL statements.

I hate to be so terse about it, but if a class module, or function, or even code behind a form dies because of a valid SQL statement, it's broken.

 

[Galaxiom]

ORDER BY [some name], [another name], [yet another]

 

[mdlueck]

As I said, if specifying multiple columns in a SQL ORDER BY clause breaks your code, your code is too brittle. Throw it out. Write code that doesn't choke on common SQL statements.

I added some custom logic to the shared code UI sort API. I look for the unique string to be passed in (name of the index) and branch elsewhere than usually.

  'Compute the correct sort condition
  If strColName = "revver" Then
    If strCurrentSort = "rev, ver" Then
      strCurrentSort = "rev DESC, ver DESC"
    Else
      strCurrentSort = "rev, ver"
    End If
  ElseIf strColName = strCurrentSort Then
    strCurrentSort = strColName & " DESC"
  Else
    strCurrentSort = strColName
  End If

  'Prepare an updated SQL based on colname clicked
  strSQL = strSQLinit & " ORDER BY " & strCurrentSort & ";"

I do likewise with the other areas of code dealing with UI controls and what not... if it is "revver" then ....

Maintains my 1 LOC standard on the forms. Best of both worlds!

 

[vbaInet]

I have created a named index which has multiple columns in said index.

By the way, where was this index created? In a table or in the Tab Index?

 

[mdlueck]

By the way, where was this index created? In a table or in the Tab Index?

Table. I have never heard of "Tab Index" in Access.

Properties of the table, click Index on the button bar.

 

[vbaInet]

So obviously you need a way of looking up the index in the table and gathering the values in the columns. Have you got this already?

How is the index selected on your form?

 

[mdlueck]

vbaInet, I actually solved this one in #10 already.

I handled it as a special case as this is the only multi-column sort needed in this application.

Handled the custom handling logic in the common shared code. The call remains a 1 LOC interface.

 

[vbaInet]

Ok, how did you grab the values? Your code doesn't reveal that.

 

[mdlueck]

Ok, how did you grab the values?

"the values???"

The call to the common shared code requires one arg, the col name to sort by. I keyed many things off of that string.

In this case, sorting by two cols, so I look for that special token to be passed, and handle that in a special way, as shown in #10. Better?

 

[vbaInet]

So by "named index" you actually mean the Indexed column in a table?

 

[mdlueck]

The call to the common shared code requires one arg, the col name to sort by. I keyed many things off of that string.

Correction to what I wrote. Here is the actual call to common code:

  Call uiutils_SetFormSort(Me, "partnumber", strSortColListArray(), strSQLinit, strCurrentSort)

So, only one arg I must change when I copy/paste. The others do not change as I use consistent naming standards.

So by "named index" you actually mean the Indexed column in a table?

Correct. Paradox had a Form way to switch to a different named index for the table grid control, so this is the very round about way to mimic that functionality.

 

[vbaInet]

Can we see the code that gets the index or the "common code" as you call it?

 

[mdlueck]

Can we see the code that gets the index or the "common code" as you call it?

It is as follows:

'Generic API for "Multiple Items" forms to provide click sortable column headings
Sub uiutils_SetFormSort(ByRef MePointer As Form, ByVal strColName As String, ByRef strSortColListArray() As String, ByVal strSQLinit As String, ByRef strCurrentSort As String)
  On Error GoTo Err_uiutils_SetFormSort

  Dim daoDB As DAO.Database
  Dim daoQDF As DAO.QueryDef
  Dim strSQL As String
  Dim strQueryDef As String
  Dim intStepCount As Integer
  Dim intStepCurrent As Integer
  Dim strThisCol As String
  Dim strThisControl As String

  'Put the mouse hourglass pointer
  Call uiutils_SetMouseHourglass

  'Compute the correct sort condition
  If strColName = "revver" Then
    If strCurrentSort = "rev, ver" Then
      strCurrentSort = "rev DESC, ver DESC"
    Else
      strCurrentSort = "rev, ver"
    End If
  ElseIf strColName = strCurrentSort Then
    strCurrentSort = strColName & " DESC"
  Else
    strCurrentSort = strColName
  End If

  'Prepare an updated SQL based on colname clicked
  strSQL = strSQLinit & " ORDER BY " & strCurrentSort & ";"

  'Get the name of the Querydef the form is using
  strQueryDef = MePointer.RecordSource

  'Attach to the FE DB
  Set daoDB = CurrentDb()

  'Loop through each QueryDef object in the FE DB
  For Each daoQDF In daoDB.QueryDefs
    If daoQDF.Name = strQueryDef Then
      'Update the query the form is using
      With daoQDF
        .SQL = strSQL
        .Close
        'We outtahere!
        Exit For
      End With
    End If
  Next

  'Update the query the form is using
  MePointer.RecordSource = strQueryDef
  MePointer.Refresh

  'Update UI to refect the sort change, if necessary
  intStepCount = Val(strSortColListArray(0))
  For intStepCurrent = 1 To intStepCount
    strThisCol = strSortColListArray(intStepCurrent)
    If strThisCol = "revver" Then
      If strColName = strThisCol Then
        'Underline on
        MePointer.Controls("Label_rev").FontUnderline = True
        MePointer.Controls("Label_ver").FontUnderline = True
      Else
        'Underline off
        MePointer.Controls("Label_rev").FontUnderline = False
        MePointer.Controls("Label_ver").FontUnderline = False
      End If
    Else
      'Calculate the name of this control
      strThisControl = "Label_" & strThisCol
      'Decide if this control is the control which sent the event message
      If strColName = strThisCol Then
        'Underline on
        MePointer.Controls(strThisControl).FontUnderline = True
      Else
        'Underline off
        MePointer.Controls(strThisControl).FontUnderline = False
      End If
    End If
  Next

Exit_uiutils_SetFormSort:
  'Clear the mouse hourglass pointer
  Call uiutils_SetMouseDefault

  Exit Sub

Err_uiutils_SetFormSort:
  Call errorhandler_MsgBox("Module: modshared_uiutils, Function: uiutils_SetFormSort()")
  Resume Exit_uiutils_SetFormSort

End Sub