Filtering using two controls

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
 
S

Steve Sanford

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...
 
S

sebastico

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compile error: Argument not optional 3
Allen Brownes ECount() 5
Search Form with a SubForm 3
Error on modified Allenbrowne SearchForm 6
Modified Allenbrowne Search Form displays error 1
Run-time error '2448' 5
Filter for words at the beginning or end using a Form Command Butt 5
Multiple Search Criteria with AND/OR??? 4

Top