Report Sort/Filter

[PC User]

I'm trying to include sorting code in a filter code. Is this possible? Can someone help me? This is what I've made so far.

Private Sub btnSetFilter_Click()
On Error Resume Next

Dim strSelect As String, strFrom As String
Dim strSQL As String, strWhere As String
Dim strOrderBy As String, strDirection As String
Dim intCounter As Integer, strRowSource As String
Dim strDate As String, strFilter As String
     
     'Build SQL String *****
     'Date Filter
        If Not IsNull([BeginningDate]) And Not IsNull([EndingDate]) Then
            If DateValue([EndingDate]) < DateValue([BeginningDate]) Then
                MsgBox "The ending date must be later than the beginning date."
            Else
                strWhere = gstrDate & " Between #" & Me.BeginningDate & "# And #" & Me.EndingDate & "#" & " And "
            End If
         End If
            
     'ComboBox Filter
        For intCounter = 1 To 6
          If Me("Filter" & intCounter) <> "" Then
            strWhere = strWhere & "[" & Me("Filter" & intCounter).tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
          End If
        Next

      'ComboBox Sort
       strOrderBy = " ORDER BY " & Me.cboSortBy & " , " & Me.cboSortOrder

     If strWhere <> "" Then
        'Strip Last " And "
        strWhere = Left(strWhere, (Len(strWhere) - 5))
        strSQL = strWhere & strOrderBy
        'Set the Filter property
        strFilter = Nz(strSQL, "")
        gstrFilter = Nz(strSQL, "")
     Else
        gstrFilter = ""
     End If
End Sub

Thanks,
PC

 

[WayneRyan]

PC,

You don't say what you get as far as errors/results, etc.

Comments:

1) strWhere = gstrDate & " Between #" & Me.BeginningDate & "# And #" & Me.EndingDate & "#" & " And "

gstrDate is a global string?

Maybe:

strWhere = "#" & gstrDate & "# Between #" & Me.BeginningDate ...

2) The Order By and filters look OK

Can't help without more info ...

Wayne

 

[PC User]

Thanks for your reply. The variables that begin with a "g" are global variables. The filter without the sort code works; so that's not the problem. There's no error message, but there is no sorting. The sort code that I added to the filter is:

'ComboBox Sort
       strOrderBy = " ORDER BY " & Me.cboSortBy & " , " & Me.cboSortOrder

and also

        strSQL = strWhere & strOrderBy

The sort comboboxes "cboSortBy" and "cboSortOrder" use Value List for their Row Source Type:
"cboSortBy" Value List is:
WorkOrder;ActionDescription;Status;Facility;Location;ResponsibleParty;DueDate;ActualStartDate
"cboSortOrder" Value List is:
Ascending;Descending
After I apply the filter and run the report, the sort code seems to be ignored and the report is generated without the sort. I am at a loss as to why its not working.

I've changed my code and put a Debug command in it and in the immediate window I get: ORDER BY [], [] when I don't put any sort requirements in the filter. I get a syntax error in the ORDER BY statement.
The revised code is:

Private Sub btnSetFilter_Click()
    On Error GoTo Whoops
    
Dim strSelect As String, strFrom As String
Dim strSQL As String, strWhere As String
Dim strOrderBy As String
Dim intCounter As Integer, strRowSource As String
Dim strDate As String, strFilter As String
     
     'Build SQL String *****
     'Date Filter
        If Not IsNull([BeginningDate]) And Not IsNull([EndingDate]) Then
            If DateValue([EndingDate]) < DateValue([BeginningDate]) Then
                MsgBox "The ending date must be later than the beginning date."
            Else
                strWhere = gstrDate & " Between #" & Me.BeginningDate & "# And #" & Me.EndingDate & "#" & " And "
            End If
         End If
            
     'ComboBox Filter
        For intCounter = 1 To 6
          If Me("Filter" & intCounter) <> "" Then
            strWhere = strWhere & "[" & Me("Filter" & intCounter).tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
          End If
        Next
        
     'ComboBox Sort
        strOrderBy = " ORDER BY " & "[" & Me.cboSortBy & "]" & ", " & "[" & Me.cboSortOrder & "]"
      Debug.Print strOrderBy
      
    If strWhere <> "" And strOrderBy <> "" Then
        'Strip Last " And "
        strWhere = Left(strWhere, (Len(strWhere) - 5))
        strSQL = strWhere & strOrderBy
        'Set the Filter property
        strFilter = Nz(strSQL, "")
        gstrFilter = Nz(strSQL, "")
    ElseIf strWhere <> "" And strOrderBy = "" Then
             'Strip Last " And "
        strWhere = Left(strWhere, (Len(strWhere) - 5))
        strSQL = strWhere
        'Set the Filter property
        strFilter = Nz(strSQL, "")
        gstrFilter = Nz(strSQL, "")
    Else
        gstrFilter = ""
    End If
    
OffRamp:
    Exit Sub
Whoops:
    MsgBox "Error #" & Err & ": " & Err.Description
    Resume OffRamp
    
End Sub

I put the conditional If statements there to try to avoid a null sort, but somethings still not right.

Thanks,
PC

 

[PC User]

Maybe it would be simpler, if I could find an example of a filter that includes a sorting code? Does anyone know of such a sample db?

Thanks,
PC

 

[PC User]

I'm trying a different approach. Instead of using a filter, I'm trying to change the recordsource. I'm still having problems with this.

Private Sub btnSetFilter_Click()
    On Error GoTo Whoops
    
Dim strSelect As String, strFrom As String
Dim strSQL As String, strWhere As String
Dim strOrderBy As String
Dim intCounter As Integer, strRowSource As String
Dim strDate As String, strFilter As String
     
     'Build SQL String *****
     'Date Filter
        If Not IsNull([BeginningDate]) And Not IsNull([EndingDate]) Then
            If DateValue([EndingDate]) < DateValue([BeginningDate]) Then
                MsgBox "The ending date must be later than the beginning date."
            Else
                strWhere = gstrDate & " Between #" & Me.BeginningDate & "# And #" & Me.EndingDate & "#" & " And "
            End If
         End If
            
     'ComboBox Filter
        For intCounter = 1 To 6
          If Me("Filter" & intCounter) <> "" Then
            strWhere = strWhere & "(" & gstrTable & Me("Filter" & intCounter).tag & ") " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
          End If
        Next
        
     'ComboBox Sort
        strOrderBy = " ORDER BY " & Me.cboSortBy & " " & Me.cboSortOrder
      
    If strWhere <> "" And strOrderBy <> "" Then
        'Strip Last " And "
        strWhere = " WHERE " & "((" & Left(strWhere, (Len(strWhere) - 5)) & ")) "
        strSQL = gstrSQL & strWhere & strOrderBy & ";"
        'Set the Filter property
        'gstrFilter = Nz(strSQL, "")
        'Set the RecordSource property
        gstrRecordSource = strSQL
      Debug.Print gstrRecordSource
    ElseIf strWhere <> "" And strOrderBy = "" Then
             'Strip Last " And "
        strWhere = " WHERE " & Left(strWhere, (Len(strWhere) - 5))
        strSQL = gstrSQL & strWhere & ";"
        'Set the Filter property
        'gstrFilter = Nz(strSQL, "")
        'Set the RecordSource property
        gstrRecordSource = strSQL
      Debug.Print gstrRecordSource
    ElseIf strWhere = "" And strOrderBy <> "" Then
        strSQL = gstrSQL & strOrderBy & ";"
        'Set the Filter property
        'gstrFilter = Nz(strSQL, "")
        'Set the RecordSource property
        gstrRecordSource = strSQL
      Debug.Print gstrRecordSource
    Else
        gstrRecordSource = ""
    End If
    
OffRamp:
    Exit Sub
Whoops:
    MsgBox "Error #" & Err & ": " & Err.Description
    Resume OffRamp
    
End Sub

All variables starting with "g" are global. After trying the filter with some date the Debug.Print results in the Immediate Window are:

SELECT tblMainData.*, * FROM tblMainData  WHERE (((tblMainData.ResponsibleParty)  = "John Doe"))  ORDER BY Facility Descending;

Am I getting my SQL script written correctly, because this isn't working.

Thanks,
PC