SQL Filter Code

[pcgenie]

Can someone help me to get the syntax correct? The global variable gstrDate is a string which can be either "[DueDate]" or "[Actual Completion Date]".
Code:
--------------------------------------------------------------------------------
strSQL = gstrDate & " Between #" & Me.BeginningDate & "# And #" & Me.EndingDate & "#) And "
--------------------------------------------------------------------------------
I used the debug.print strSQL, and it shows strSQL = "" . My code for the dates goes like this:
Code:
--------------------------------------------------------------------------------
If Not IsNull(IsDate(BeginningDate)) And Not IsNull(IsDate(EndingDate)) Then
If EndingDate < BeginningDate Then
MsgBox "The ending date must be later than the beginning date."
End If
Else
strSQL = gstrDate & " Between #" & Me.BeginningDate & "# And #" & Me.EndingDate & "#) And "
Debug.Print strSQL
End If
--------------------------------------------------------------------------------
There must be some detail that I'm missing that will get it to work.
Thanks,
PC

 

[PHV]

Where is strSQL defined ?
Have you Option Explicit at the beginning of the module ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pcgenie]

Good question. I should post the entire code for the filter.
Code:
--------------------------------------------------------------------------------
Private Sub btnSetFilter_Click()
On Error Resume Next

Dim strSelect As String, strFrom As String
Dim strSQL As String, strWhere As String
Dim intCounter As Integer, strRowSource As String
Dim strDate As String, strFilter As String

'Build SQL String *****
'Date Filter
If Not IsNull(IsDate(BeginningDate)) And Not IsNull(IsDate(EndingDate)) Then
If EndingDate < BeginningDate Then
MsgBox "The ending date must be later than the beginning date."
End If
Else
strSQL = gstrDate & "Between #" & Me.BeginningDate & "# And #" & Me.EndingDate & "#) And "
End If

'Combobox Filter
For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
Debug.Print strSQL
'Set the Filter property
strFilter = Nz(strSQL, "")
gstrFilter = Nz(strSQL, "")
Else
gstrFilter = ""
End If
End Sub
--------------------------------------------------------------------------------
Thanks for your reply,
PC

 

[pcgenie]

Ok, I got the filter to work. Now I need to get the syntax right for the "sort order". Any suggestions?

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
        'Combine Sort and Order
        'strOrderBy = "[" & Me.cboSortBy & "]" & " " & Me.cboSortOrder.Column(1)
        strOrderBy = Me.cboSortBy & " " & Me.cboSortOrder.Column(1)
        strOrderBy = strOrderBy & ", "

      
        If Me.cboSortBy <> "" Then
            ' Strip Last Comma & Space.
            strSQL = Left(strOrderBy, (Len(strOrderBy) - 2))
            'Set the OrderBy property.
            gstrOrderBy = Nz(strSQL, "")
            'Debug.Print gstrOrderBy
        Else
            gstrOrderBy = ""
        End If
        
        If strWhere <> "" Then
           'Strip Last " And "
           strSQL = Left(strWhere, (Len(strWhere) - 5))
           'Set the Filter property
           gstrFilter = Nz(strSQL, "")
           'Debug.Print gstrFilter
        Else
           gstrFilter = ""
        End If
     
OffRamp:
    Exit Sub
Whoops:
    MsgBox "Error #" & Err & ": " & Err.Description
    Resume OffRamp
    
End Sub

Thanks,
PC