Filtering using two controls

  • Thread starter Thread starter sebastico
  • Start date Start date
S

sebastico

Hello
I have a form modified from Allen's Brownw Search2000 db. In the form I have
the choice to filtering by years or filtering using checkboxes. This is my
code

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Dim strTmp As String


'Filter by years using txtboxes

If Not IsNull(Me.txtStartYyear) Then
strWhere = strWhere & "([Yyear] >= """ & (Me.txtStartYyear) & """)
AND "
End If

If Not IsNull(Me.txtEndYyear) Then
strWhere = strWhere & "([Yyear] <= """ & (Me.txtEndYyear) & """) AND
"
End If

Me.FilterOn = False
End If

strTmp = ""

'Filter using checkboxes
If Me.chkBio.Value Then
strTmp = "'1',"
End If

If Me.chkBIE.Value Then
strTmp = strTmp & "'2',"
End If

If Me.chkCons.Value Then
strTmp = strTmp & "'3',"
End If


lngLen = Len(strTmp) - 1

If lngLen > 0 Then
strWhere = "FacetID IN (" & Left$(strTmp, lngLen) & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND
" at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True

End If

End Sub


The code allow me to filter by txtboxes and checkboxes
For instance:
If I click checkboxBIO vba displays 5 records
If I click checkboxBIE vba displays 10 records
If I click checkboxBIO and checkboxBIE VBA dispalys 15 records

On the other hand if I filter by two years VBA displays for example 451
records
Now I'm facing this problem:
If I enter two years and I click in checkboxBIO VBA displays 5 records
If I enter two years and I click in checkboxBIO and checkboxBIE VBA dispalys
15 recordsvba


What I need is:
If I use years and ckeckboxes VBA will displays all records within years and
checkboxes
If I use only years VBA will displays all records only within years
If I use only checkboxes VBA will displays all records related to checkboxes


I have been trying this code with no success

If Not IsNull(Me.txtStartYyear) Then
strWhere = strWhere & "([Yyear] >= """ & (Me.txtStartYyear) & """)
AND "

If Me.chkBio.Value Then
strTmp = "'1',"
End If

If Me.chkBIE.Value Then
strTmp = strTmp & "'2',"
End If

If Me.chkCons.Value Then
strTmp = strTmp & "'3',"
End If

If Not IsNull(Me.txtEndYyear) Then
strWhere = strWhere & "([Yyear] <= """ & (Me.txtEndYyear) & """) AND
"
If Me.chkBio.Value Then
strTmp = "'1',"
End If

If Me.chkBIE.Value Then
strTmp = strTmp & "'2',"
End If

If Me.chkCons.Value Then
strTmp = strTmp & "'3',"
End If

End If


End If

I have been searching the vba help, googling the web with no success. Even
more, I asked programmers and college teachers of programming and they said
they don't work with this programs. Even I tried explaining the experts with
algorithm, Oriented Objects Algorithm and neither.
Any ideas are really welcome
Many thanks
 
You were wiping out the filter and you had one too many "End If" statements.

In the table, is the field type for [Yyear] a text type??

Is the field type for the field [FacetID] a text type??


Try this modification to your code:

'-----------beg code ---------------------
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Dim strTmp As String

Me.FilterOn = False

'Filter by years using txtboxes

If Not IsNull(Me.txtStartYyear) Then
strWhere = strWhere & "[Yyear] >= '" & Me.txtStartYyear & "' AND "
End If

If Not IsNull(Me.txtEndYyear) Then
strWhere = strWhere & "[Yyear] <= '" & Me.txtEndYyear & "' AND "
End If

strTmp = ""

'Filter using checkboxes
If Me.chkBio.Value Then
strTmp = "'1',"
End If

If Me.chkBIE.Value Then
strTmp = strTmp & "'2',"
End If

If Me.chkCons.Value Then
strTmp = strTmp & "'3',"
End If


lngLen = Len(strTmp) - 1

If lngLen > 0 Then
strWhere = strWhere & "FacetID IN (" & Left$(strTmp, lngLen) & ")
AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
' Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True

End If


End Sub
'-----------end code ---------------------



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


sebastico said:
Hello
I have a form modified from Allen's Brownw Search2000 db. In the form I have
the choice to filtering by years or filtering using checkboxes. This is my
code

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Dim strTmp As String


'Filter by years using txtboxes

If Not IsNull(Me.txtStartYyear) Then
strWhere = strWhere & "([Yyear] >= """ & (Me.txtStartYyear) & """)
AND "
End If

If Not IsNull(Me.txtEndYyear) Then
strWhere = strWhere & "([Yyear] <= """ & (Me.txtEndYyear) & """) AND
"
End If

Me.FilterOn = False
End If

strTmp = ""

'Filter using checkboxes
If Me.chkBio.Value Then
strTmp = "'1',"
End If

If Me.chkBIE.Value Then
strTmp = strTmp & "'2',"
End If

If Me.chkCons.Value Then
strTmp = strTmp & "'3',"
End If


lngLen = Len(strTmp) - 1

If lngLen > 0 Then
strWhere = "FacetID IN (" & Left$(strTmp, lngLen) & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND
" at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True

End If

End Sub


The code allow me to filter by txtboxes and checkboxes
For instance:
If I click checkboxBIO vba displays 5 records
If I click checkboxBIE vba displays 10 records
If I click checkboxBIO and checkboxBIE VBA dispalys 15 records

On the other hand if I filter by two years VBA displays for example 451
records
Now I'm facing this problem:
If I enter two years and I click in checkboxBIO VBA displays 5 records
If I enter two years and I click in checkboxBIO and checkboxBIE VBA dispalys
15 recordsvba


What I need is:
If I use years and ckeckboxes VBA will displays all records within years and
checkboxes
If I use only years VBA will displays all records only within years
If I use only checkboxes VBA will displays all records related to checkboxes


I have been trying this code with no success

If Not IsNull(Me.txtStartYyear) Then
strWhere = strWhere & "([Yyear] >= """ & (Me.txtStartYyear) & """)
AND "

If Me.chkBio.Value Then
strTmp = "'1',"
End If

If Me.chkBIE.Value Then
strTmp = strTmp & "'2',"
End If

If Me.chkCons.Value Then
strTmp = strTmp & "'3',"
End If

If Not IsNull(Me.txtEndYyear) Then
strWhere = strWhere & "([Yyear] <= """ & (Me.txtEndYyear) & """) AND
"
If Me.chkBio.Value Then
strTmp = "'1',"
End If

If Me.chkBIE.Value Then
strTmp = strTmp & "'2',"
End If

If Me.chkCons.Value Then
strTmp = strTmp & "'3',"
End If

End If


End If

I have been searching the vba help, googling the web with no success. Even
more, I asked programmers and college teachers of programming and they said
they don't work with this programs. Even I tried explaining the experts with
algorithm, Oriented Objects Algorithm and neither.
Any ideas are really welcome
Many thanks
Click to expand...
 
Steve
I'm so sorry for my delay in writing you. I was away form home
Your code works. Now
In the table, is the field type for [Yyear] a text type?? Yes it it

Is the field type for the field [FacetID] a text type?? Yes it it

However, If I enter data in in textboxes (StartYyear and EndYyear) and If I
click in check vba displays only the records from the checkboxes. I need
thevba displays all record within period of years and the checkbox

Could you suggest how to do it?

Thanks again
Steve Sanford said:
You were wiping out the filter and you had one too many "End If" statements.

In the table, is the field type for [Yyear] a text type??

Is the field type for the field [FacetID] a text type??


Try this modification to your code:

'-----------beg code ---------------------
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Dim strTmp As String

Me.FilterOn = False

'Filter by years using txtboxes

If Not IsNull(Me.txtStartYyear) Then
strWhere = strWhere & "[Yyear] >= '" & Me.txtStartYyear & "' AND "
End If

If Not IsNull(Me.txtEndYyear) Then
strWhere = strWhere & "[Yyear] <= '" & Me.txtEndYyear & "' AND "
End If

strTmp = ""

'Filter using checkboxes
If Me.chkBio.Value Then
strTmp = "'1',"
End If

If Me.chkBIE.Value Then
strTmp = strTmp & "'2',"
End If

If Me.chkCons.Value Then
strTmp = strTmp & "'3',"
End If


lngLen = Len(strTmp) - 1

If lngLen > 0 Then
strWhere = strWhere & "FacetID IN (" & Left$(strTmp, lngLen) & ")
AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
' Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True

End If


End Sub
'-----------end code ---------------------



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


sebastico said:
Hello
I have a form modified from Allen's Brownw Search2000 db. In the form I have
the choice to filtering by years or filtering using checkboxes. This is my
code

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Dim strTmp As String


'Filter by years using txtboxes

If Not IsNull(Me.txtStartYyear) Then
strWhere = strWhere & "([Yyear] >= """ & (Me.txtStartYyear) & """)
AND "
End If

If Not IsNull(Me.txtEndYyear) Then
strWhere = strWhere & "([Yyear] <= """ & (Me.txtEndYyear) & """) AND
"
End If

Me.FilterOn = False
End If

strTmp = ""

'Filter using checkboxes
If Me.chkBio.Value Then
strTmp = "'1',"
End If

If Me.chkBIE.Value Then
strTmp = strTmp & "'2',"
End If

If Me.chkCons.Value Then
strTmp = strTmp & "'3',"
End If


lngLen = Len(strTmp) - 1

If lngLen > 0 Then
strWhere = "FacetID IN (" & Left$(strTmp, lngLen) & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND
" at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True

End If

End Sub


The code allow me to filter by txtboxes and checkboxes
For instance:
If I click checkboxBIO vba displays 5 records
If I click checkboxBIE vba displays 10 records
If I click checkboxBIO and checkboxBIE VBA dispalys 15 records

On the other hand if I filter by two years VBA displays for example 451
records
Now I'm facing this problem:
If I enter two years and I click in checkboxBIO VBA displays 5 records
If I enter two years and I click in checkboxBIO and checkboxBIE VBA dispalys
15 recordsvba


What I need is:
If I use years and ckeckboxes VBA will displays all records within years and
checkboxes
If I use only years VBA will displays all records only within years
If I use only checkboxes VBA will displays all records related to checkboxes


I have been trying this code with no success

If Not IsNull(Me.txtStartYyear) Then
strWhere = strWhere & "([Yyear] >= """ & (Me.txtStartYyear) & """)
AND "

If Me.chkBio.Value Then
strTmp = "'1',"
End If

If Me.chkBIE.Value Then
strTmp = strTmp & "'2',"
End If

If Me.chkCons.Value Then
strTmp = strTmp & "'3',"
End If

If Not IsNull(Me.txtEndYyear) Then
strWhere = strWhere & "([Yyear] <= """ & (Me.txtEndYyear) & """) AND
"
If Me.chkBio.Value Then
strTmp = "'1',"
End If

If Me.chkBIE.Value Then
strTmp = strTmp & "'2',"
End If

If Me.chkCons.Value Then
strTmp = strTmp & "'3',"
End If

End If


End If

I have been searching the vba help, googling the web with no success. Even
more, I asked programmers and college teachers of programming and they said
they don't work with this programs. Even I tried explaining the experts with
algorithm, Oriented Objects Algorithm and neither.
Any ideas are really welcome
Many thanks
Click to expand...
Click to expand...
 
Back
Top