Creat a filter button.

[injanib via AccessMonster.com]

How can I creat a button on the form that can perform a form filter instead
of haing to right click on the form or select form filter from the record
menue. I want to creat three buttons. one to creat the filter, one to apply
the filter and one to clear the filter. I only need help for the first one.
Thanks all.

 

[Allen Browne]

To create a filter for your form, your code will need to generate a string
that looks just like the WHERE clause in a query. You can then assign that
string to the Filter property of the report, and set FilterOn as true.

This example assumes a command button named cmdFilter, and when you click it
your form is filtered to only the records where the Amount field is more
than 1000:

Private Sub cmdFilter_Click()
Dim strWhere As String
strWhere = "[Amount] > 1000"
Me.Filter = strWhere
Me.FilterOn = True
End Sub

Here's a more comprehensive example, that shows how to build the filter
string from lots of unbound text boxes of different data types:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[injanib via AccessMonster.com]

thanks for the reference.
It works only for the dates fields, but the combo box and number field gives
me the following error.

Run-time error '2448';
you can't assign a value to this object.

the code is as follows.

Option Compare Database
Option Explicit

Private Sub cmdSearchDate_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Start Date] = " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([End Date] = " & Format(Me.txtEndDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtInvoiceNumber) Then
strWhere = strWhere & "([Invoice Number] = " & Me.txtInvoiceNumber &
")"
End If


If Me.txtRSAType = -1 Then
strWhere = strWhere & "([RSA Type] = True) AND "
ElseIf Me.txtRSAType = 0 Then
strWhere = strWhere & "([RSA Type] = False)"
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Please Select a criteria."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

To create a filter for your form, your code will need to generate a string
that looks just like the WHERE clause in a query. You can then assign that
string to the Filter property of the report, and set FilterOn as true.

This example assumes a command button named cmdFilter, and when you click it
your form is filtered to only the records where the Amount field is more
than 1000:

Private Sub cmdFilter_Click()
Dim strWhere As String
strWhere = "[Amount] > 1000"
Me.Filter = strWhere
Me.FilterOn = True
End Sub

Here's a more comprehensive example, that shows how to build the filter
string from lots of unbound text boxes of different data types:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

How can I create a button on the form that can perform a form filter
instead

[quoted text clipped - 3 lines]

the filter and one to clear the filter. I only need help for the first
one.

 

[Allen Browne]

Which line gives the error?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thanks for the reference.
It works only for the dates fields, but the combo box and number field
gives
me the following error.

Run-time error '2448';
you can't assign a value to this object.

the code is as follows.

Option Compare Database
Option Explicit

Private Sub cmdSearchDate_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Start Date] = " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([End Date] = " & Format(Me.txtEndDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtInvoiceNumber) Then
strWhere = strWhere & "([Invoice Number] = " & Me.txtInvoiceNumber
&
")"
End If


If Me.txtRSAType = -1 Then
strWhere = strWhere & "([RSA Type] = True) AND "
ElseIf Me.txtRSAType = 0 Then
strWhere = strWhere & "([RSA Type] = False)"
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Please Select a criteria."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

To create a filter for your form, your code will need to generate a string
that looks just like the WHERE clause in a query. You can then assign that
string to the Filter property of the report, and set FilterOn as true.

This example assumes a command button named cmdFilter, and when you click
it
your form is filtered to only the records where the Amount field is more
than 1000:

Private Sub cmdFilter_Click()
Dim strWhere As String
strWhere = "[Amount] > 1000"
Me.Filter = strWhere
Me.FilterOn = True
End Sub

Here's a more comprehensive example, that shows how to build the filter
string from lots of unbound text boxes of different data types:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

How can I create a button on the form that can perform a form filter
instead

[quoted text clipped - 3 lines]

the filter and one to clear the filter. I only need help for the first
one.

 

[injanib via AccessMonster.com]

the error occurs at this line.

Me.Filter = strWhere

I have two date fields, Start Date and End Date, for which the code works
just fine.
then I have an number field in which I just type in an invoice number and get
the record for that invoice. The combo box (RSA Type) is sourced from the
same table that the (RSA Type) field is based on. I chose a crieteria from
this list and apply it to the form. But everytime I include the codes for
these two fields the filter gives me that error.

Which line gives the error?

thanks for the reference.
It works only for the dates fields, but the combo box and number field

[quoted text clipped - 75 lines]

 

[Allen Browne]

Okay, the error message indicates that the string is not correctly formed,
and so cannot be assigned to the form's filter.

Just above that line, enter:
Debug.Print strWhere

When it fails, open the Immediate Window (Ctrl+G), and see what printed
there. You need to correct your code so it generates a value WHERE clause.

If you have spaces in your field names, enclose them in square brackets,
e.g.:
[Start Date]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

the error occurs at this line.

Me.Filter = strWhere

I have two date fields, Start Date and End Date, for which the code works
just fine.
then I have an number field in which I just type in an invoice number and
get
the record for that invoice. The combo box (RSA Type) is sourced from the
same table that the (RSA Type) field is based on. I chose a crieteria from
this list and apply it to the form. But everytime I include the codes for
these two fields the filter gives me that error.

Which line gives the error?

thanks for the reference.
It works only for the dates fields, but the combo box and number field

[quoted text clipped - 75 lines]

the filter and one to clear the filter. I only need help for the first
one.

 

[injanib via AccessMonster.com]

Ok, here is the entire code.

Option Compare Database
Option Explicit

Private Sub cmdSearchDate_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Start Date] = " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([End Date] = " & Format(Me.txtEndDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtInvoiceNumber) Then
strWhere = strWhere & "([Invoice Number] = " & Me.txtInvoiceNumber &
") AND "
End If

If Me.txtInvoiceStatus = -1 Then
strWhere = strWhere & "([Invoice Status] = True) AND "
ElseIf Me.txtInvoiceStatus = 0 Then
strWhere = strWhere & "([Invoice Status] = False) AND"
End If

If Me.txtRSAType = -1 Then
strWhere = strWhere & "([RSA Type] = True) AND "
ElseIf Me.txtRSAType = 0 Then
strWhere = strWhere & "([RSA Type] = False) AND"
End If

If Me.txtStaffingType = -1 Then
strWhere = strWhere & "([Staffing Type] = True) AND "
ElseIf Me.txtStaffingType = 0 Then
strWhere = strWhere & "([Staffing Type] = False)AND"
End If

If Not IsNull(Me.txtEmployeeOut) Then
strWhere = strWhere & "([Employee Out] = """ & Me.txtEmployeeOut &
""") AND "
End If

If Me.txtSiteName = -1 Then
strWhere = strWhere & "([Site Name] = True) AND "
ElseIf Me.txtSiteName = 0 Then
strWhere = strWhere & "([Site Name] = False)"
End If

lngLen = Len(strWhere) - 1
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "Please select a criteria", vbInformation, "No Criteria!"
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

here is each criteria field I use each time and their respective error in the
imidiate window.

Start Date + End Date
([Start Date] = #01/22/2007#) AND ([End Date] = #01/22/2007#)AND

Invoice Number
([Invoice Number] = 1078665)AND

Employee Out
([Employee Out] = "Jaron Mclurin") AND

But when I use the combo box fields the code does not look into those fields
and if the other fields are blank then it will disply the "No Criteria"
message.

As you can see all my field names that have a space are closed in brackets.
And I have looked several times to make sure that the field names and the
crieteria field names are consistent with those in the code. Thanks again for
your responses. Please look if you can see something I haven't been able to.

Okay, the error message indicates that the string is not correctly formed,
and so cannot be assigned to the form's filter.

Just above that line, enter:
Debug.Print strWhere

When it fails, open the Immediate Window (Ctrl+G), and see what printed
there. You need to correct your code so it generates a value WHERE clause.

If you have spaces in your field names, enclose them in square brackets,
e.g.:
[Start Date]

the error occurs at this line.

[quoted text clipped - 16 lines]

 

[Allen Browne]

Post the result of the line:
Debug.Print strWhere

That will provide the clue as to where your code is not generating the
correct results.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ok, here is the entire code.

Option Compare Database
Option Explicit

Private Sub cmdSearchDate_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Start Date] = " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([End Date] = " & Format(Me.txtEndDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtInvoiceNumber) Then
strWhere = strWhere & "([Invoice Number] = " & Me.txtInvoiceNumber
&
") AND "
End If

If Me.txtInvoiceStatus = -1 Then
strWhere = strWhere & "([Invoice Status] = True) AND "
ElseIf Me.txtInvoiceStatus = 0 Then
strWhere = strWhere & "([Invoice Status] = False) AND"
End If

If Me.txtRSAType = -1 Then
strWhere = strWhere & "([RSA Type] = True) AND "
ElseIf Me.txtRSAType = 0 Then
strWhere = strWhere & "([RSA Type] = False) AND"
End If

If Me.txtStaffingType = -1 Then
strWhere = strWhere & "([Staffing Type] = True) AND "
ElseIf Me.txtStaffingType = 0 Then
strWhere = strWhere & "([Staffing Type] = False)AND"
End If

If Not IsNull(Me.txtEmployeeOut) Then
strWhere = strWhere & "([Employee Out] = """ & Me.txtEmployeeOut &
""") AND "
End If

If Me.txtSiteName = -1 Then
strWhere = strWhere & "([Site Name] = True) AND "
ElseIf Me.txtSiteName = 0 Then
strWhere = strWhere & "([Site Name] = False)"
End If

lngLen = Len(strWhere) - 1
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "Please select a criteria", vbInformation, "No Criteria!"
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

here is each criteria field I use each time and their respective error in
the
imidiate window.

Start Date + End Date
([Start Date] = #01/22/2007#) AND ([End Date] = #01/22/2007#)AND

Invoice Number
([Invoice Number] = 1078665)AND

Employee Out
([Employee Out] = "Jaron Mclurin") AND

But when I use the combo box fields the code does not look into those
fields
and if the other fields are blank then it will disply the "No Criteria"
message.

As you can see all my field names that have a space are closed in
brackets.
And I have looked several times to make sure that the field names and the
crieteria field names are consistent with those in the code. Thanks again
for
your responses. Please look if you can see something I haven't been able
to.

Okay, the error message indicates that the string is not correctly formed,
and so cannot be assigned to the form's filter.

Just above that line, enter:
Debug.Print strWhere

When it fails, open the Immediate Window (Ctrl+G), and see what printed
there. You need to correct your code so it generates a value WHERE clause.

If you have spaces in your field names, enclose them in square brackets,
e.g.:
[Start Date]

the error occurs at this line.

[quoted text clipped - 16 lines]

the filter and one to clear the filter. I only need help for the
first
one.

 

[injanib via AccessMonster.com]

I already did, but here it is again.

when I use the combination of Start Date and End Date:
the error occurs at:

Me.Filter = strWhere

the result of (Debug.Print strWhere) is
([Start Date] = #01/22/2007#) AND ([End Date] = #01/22/2007#)AND

when I use the Invoice Number, the result is:
([Invoice Number] = 1078665)AND

when I use the Employee Out field, the result is
([Employee Out] = "Jaron Mclurin") AND

But when I use the combo box fields the code does not look into those fields
and if the other fields are blank then it will disply the "No Criteria"
message.

Post the result of the line:
Debug.Print strWhere

That will provide the clue as to where your code is not generating the
correct results.

Ok, here is the entire code.

[quoted text clipped - 110 lines]

 

[Allen Browne]

There's a trailing "AND " at the end of the string. You need to remove that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I already did, but here it is again.

when I use the combination of Start Date and End Date:
the error occurs at:

Me.Filter = strWhere

the result of (Debug.Print strWhere) is
([Start Date] = #01/22/2007#) AND ([End Date] = #01/22/2007#)AND

when I use the Invoice Number, the result is:
([Invoice Number] = 1078665)AND

when I use the Employee Out field, the result is
([Employee Out] = "Jaron Mclurin") AND

But when I use the combo box fields the code does not look into those
fields
and if the other fields are blank then it will disply the "No Criteria"
message.

Post the result of the line:
Debug.Print strWhere

That will provide the clue as to where your code is not generating the
correct results.

Ok, here is the entire code.

[quoted text clipped - 110 lines]

first
one.

 

[injanib via AccessMonster.com]

it actually turned out that it was missing an "AND" at the end. and I needed
to put spaces between the "AND" and the quotation marks.

Thanks for all the help.

There's a trailing "AND " at the end of the string. You need to remove that.

I already did, but here it is again.

[quoted text clipped - 28 lines]