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]

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