Custom SQL Order By.

[Tyler F]

I'm trying to create a custom order by that builds a statement based on the order of the array. I'm starting to think that SQL Case statement is not supported by MS Access. Or possibly there is a syntax error, on my part.
Here is the String building loop..

    strSQL = "Case [" & FieldName & "] "

    For z = intFirst To intLast
    
        strSQL = strSQL & _
            "WHEN " & SortOrder(z) & _
            " THEN " & forCount & " "
        
        forCount = forCount + 1
    
    Next z

    strSQL = strSQL & "END;"

Then i set the query.SQL property to this string. And it gives error 3075 Syntax error (missing operator).

Here is the main sql statement.

    sqlSort = "SELECT * FROM [table1] ORDER BY (" & _
        CustomSort(Sorts(), FieldName) & ")"

CustomSort is a function, returns string.

If this method is not supported, is there a way to do this in access, without adding a separate identifying column?
I just really want a generic and quick way to do this.

[Tyler F]

Append field method

I couldn't find any other way, so for conclusion I am using this function... the 'DoesFieldExist ' function is another function. GetFirstPos and GetLastPos is so that I can throw any array at it. I just have an order by sql clause that sorts by the _sort column.

Function CustomSortField(ByRef SortOrder() As String, FieldName As String, TableName As String) As String
    
    Dim intFirst As Integer, intLast As Integer
    Dim strSQL As String, z As Integer, forCount As Integer
    Dim Tbl As TableDef, fld As Field, Rst As Recordset, dbs As DAO.Database
    
    '===
    'Get First Position
    'Generalization
    '===
    intFirst = GetFirstPos(SortOrder())
    
    '===
    'Get Last Position
    'Generalization
    '===
    intLast = GetLastPos(SortOrder())
    '===============================
    Set dbs = CurrentDb
    
    DoCmd.Close acTable, TableName, acSaveYes
    '===
    'Add Column to end of table.
    '===
    If DoesFieldExist(TableName, FieldName & "_Sort") = True Then _
        DoCmd.RunSQL "ALTER TABLE [" & TableName & "] DROP COLUMN [" & FieldName & "_Sort" & "]"
        
        Set Tbl = dbs.TableDefs(TableName)
        
        'Create New column
        Set fld = Tbl.CreateField(FieldName & "_Sort", dbLong)
        'fld.Properties("ColumnHidden").Value = True
        Tbl.Fields.Append fld
        
        Set fld = dbs.TableDefs(TableName).Fields(FieldName)
        
        Set fld = Nothing
        Set Tbl = Nothing
        
        
    forCount = 0
    
    Set Rst = dbs.OpenRecordset(TableName)
    
    For z = intFirst To intLast
    
        With Rst
        If Rst.RecordCount = 0 Then GoTo ZeroRecord
            .MoveFirst
            
            Do
            
                If Left(.Fields(FieldName).Value, Len(SortOrder(z))) = SortOrder(z) Then
                
                    .Edit
                    .Fields(FieldName & "_Sort").Value = forCount
                    .Update
                
                End If
                
                .MoveNext
                
            Loop Until .EOF
        End With
        
        forCount = forCount + 1
    
    Next z
    
    CustomSortField = FieldName & "_Sort"
    
    Exit Function
ZeroRecord:
    CustomSortField = ""
    
End Function

Private Function GetFirstPos(ByRef acArray() As String)

    intFirst = -1
    
    Do
    
        intFirst = intFirst + 1
        
    Loop Until acArray(intFirst) <> ""
    
    GetFirstPos = intFirst


End Function

Private Function GetLastPos(ByRef acArray() As String) As Integer
    Dim intLast As Integer
    
    On Error GoTo OverFlowArray
    Do
        
        Debug.Print acArray(intLast)
        intLast = intLast + 1
        
    Loop
    
FindLastPos:
    intLast = intLast - 1
    On Error GoTo 0
    
    Do
    
        intLast = intLast - 1
        
    Loop Until acArray(intLast) <> ""
    
    GetLastPos = intLast
    
ExitHere:
    Exit Function
OverFlowArray:
    Err.Clear
    Resume FindLastPos
End Function