Building A Filter

[PC User]

I have a filter on a form for my reports and it consists of: 6
comboboxes and 2 date fields. The user may use one or all the filters
for reports of the same recordsource. The reports are of two catagories
indicated by their name's prefix "rwo" or "rpg". One of the filter
comboboxes (Filter5) has to change its rowsource when swithching
between report catagories. I'm having trouble building the filter and
getting it to work. The date filter is not being included in the
filter and Filter5 is not working. Please help.

==========================================
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

'Build SQL String *****
If Left(gstrReport, 3) = "rwo" Then
strDate = "ActualStartDate"             'Work Order Start
Dates
strSelect = "SELECT DISTINCT
tblMainData.ActionDescription "
strFrom = "FROM tblMainData "
strWhere = "ORDER BY tblMainData.ActionDescription;"
strRowSource = strSelect & strFrom & strWhere
ElseIf Left(gstrReport, 3) = "rpg" Then
strDate = "DueDate"                     'Program Due Dates
strSelect = "SELECT DISTINCT
tsubProgramList.ProgramDescription "
strFrom = "FROM tsubProgramList "
strWhere = "ORDER BY
tsubProgramList.ProgramDescription;"
strRowSource = strSelect & strFrom & strWhere
End If
Me!Filter5.RowSource = strRowSource

'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 = "([CDate(strDate)] 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))
'Set the Filter property
gstrFilter = Nz(strSQL, "")

Else
gstrFilter = ""
End If
End Sub

==========================================

 

[Guest]

I meant strSQL not strQuery

 

[Guest]

For some reason you are changing from strSelect to strQuery when doing the
dates, that may be part of the problem

 

[PC User]

I've changed the date filter, but I'm having trouble with syntax.
gstrDate is a global variable which can be either "[DueDate]" or
"[ActualStartDate]".
=======================
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
'strSQL = gstrDate & " Between #" & Me.BeginningDate & "# And #" &
Me.EndingDate & "# And "
strSQL = gstrDate & " Between " & Me.BeginningDate & " And " &
Me.EndingDate & " And "
End If
End If
=======================
I've tried these two statements, but I can't get the syntax right. Any
suggestions?
=======================
strSQL = gstrDate & " Between #" & Me.BeginningDate & "# And #" &
Me.EndingDate & "# And "
strSQL = gstrDate & " Between " & Me.BeginningDate & " And " &
Me.EndingDate & " And "
=======================
Thanks,
PC