Filter button on Yes/No field

  • Thread starter Thread starter Sammy
  • Start date Start date
S

Sammy

What's the code to create a command button which toggles
a filter by the "completed" field, which is a yes/no
value? So click the button to view all records with
completed value = yes, and click again to view completed
value = no. Thanks!
 
Try this in the button's Click event:

Me.Filter = "Completed"
Me.FilterOn = Not Me.FilterOn

Good luck,
Pavel
 
Thanks. This is a start. Clicking my button once turns
the filter completed=yes on. Clicking it a second time
takes the filter off, so I see completed=yes and
completed=no. Is it possible for the second click to
change the filter from completed=yes to completed=no? I
want to be able to see either all completed records or
all incompleted records. I really don't need to see both
at the same time.
 
Try this in the button's Click event:

if Me.Filter = "Completed" then _
Me.Filter = "Completed = 0" else _
Me.Filter = "Completed"
Me.FilterOn = True

Good luck,
Pavel
 
Beautiful! Thanks so much!

I have a follow-up question to your previous reply. The
code you provided me turned the filter on and off:
Me.Filter = "completed"
Me.FilterOn = Not Me.FilterOn

I would like to create a filter using buttons on other
forms where the values in the field are not yes/no. For
example, my field is called "status" and the values can
be "complete" "Partial" "incomplete" and null. What
would the filter command be to filter the "partial"
records? What would be the command to filter null
records?
Thanks.
 
If you follow the logic in my code suggestions, you will be able to
easily build your own, but I would recommend using another way of
choosing the option than a command button. I think it would be best to
use a combo box that would list the available choices, in this case
Complete, Incomplete, Partial. You can do that by setting the combo box
row source to a statement like

SELECT DISTINCT status FROM MyTable

Then, you can create the filter in one step by putting the following in
that combo box AfterUpdate event:

Me.Filter = "status = '" & Me.MyComboBox & "'"

To get rid of Null values, try:

Me.Filter = "IsNull(status) = False" or
Me.Filter = "Not Is Null status"

although I would prefer to filter the null values in the form's record
source beforehand.
Good luck,
Pavel
 
Ok - I like the idea of a combo box, but I can't get the
filter to run. I'm going to forget the null values for
now.

Name of table containing status list is "PoStatus" (It
has one field called status which contains the values
complete, partial, incomplete) ?Would a value list as a
row source in the combo box do the same thing? I tried
this first but when it didn't work, I tried the table
like you suggested. Still no joy.

Combo box name: combo196
Row source: SELECT DISTINCT [postatus].[Status] FROM
postatus;
AfterUpdate event:
Me.Filter = "POstat ='" & Me.Combo196 & "'"


The code picks up the value of the combo box, but doesn't
apply the filter.

I hope you can help. Thanks.
 
Do you have

Me.FilterOn = True

in the AfterUpdate? This turns the filter On. Setting the filter value
alone is not sufficient.
Pavel
Ok - I like the idea of a combo box, but I can't get the
filter to run. I'm going to forget the null values for
now.

Name of table containing status list is "PoStatus" (It
has one field called status which contains the values
complete, partial, incomplete) ?Would a value list as a
row source in the combo box do the same thing? I tried
this first but when it didn't work, I tried the table
like you suggested. Still no joy.

Combo box name: combo196
Row source: SELECT DISTINCT [postatus].[Status] FROM
postatus;
AfterUpdate event:
Me.Filter = "POstat ='" & Me.Combo196 & "'"

The code picks up the value of the combo box, but doesn't
apply the filter.

I hope you can help. Thanks.
-----Original Message-----
If you follow the logic in my code suggestions, you will be able to
easily build your own, but I would recommend using another way of
choosing the option than a command button. I think it would be best to
use a combo box that would list the available choices, in this case
Complete, Incomplete, Partial. You can do that by setting the combo box
row source to a statement like

SELECT DISTINCT status FROM MyTable

Then, you can create the filter in one step by putting the following in
that combo box AfterUpdate event:

Me.Filter = "status = '" & Me.MyComboBox & "'"

To get rid of Null values, try:

Me.Filter = "IsNull(status) = False" or
Me.Filter = "Not Is Null status"

although I would prefer to filter the null values in the form's record
source beforehand.
Good luck,
Pavel

.
Click to expand...
Click to expand...
 
Thanks so much. It works.
-----Original Message-----
Do you have

Me.FilterOn = True

in the AfterUpdate? This turns the filter On. Setting the filter value
alone is not sufficient.
Pavel
Ok - I like the idea of a combo box, but I can't get the
filter to run. I'm going to forget the null values for
now.

Name of table containing status list is "PoStatus" (It
has one field called status which contains the values
complete, partial, incomplete) ?Would a value list as a
row source in the combo box do the same thing? I tried
this first but when it didn't work, I tried the table
like you suggested. Still no joy.

Combo box name: combo196
Row source: SELECT DISTINCT [postatus].[Status] FROM
postatus;
AfterUpdate event:
Me.Filter = "POstat ='" & Me.Combo196 & "'"

The code picks up the value of the combo box, but doesn't
apply the filter.

I hope you can help. Thanks.
-----Original Message-----
If you follow the logic in my code suggestions, you
Click to expand...
will
be able to
easily build your own, but I would recommend using another way of
choosing the option than a command button. I think it would be best to
use a combo box that would list the available choices, in this case
Complete, Incomplete, Partial. You can do that by setting the combo box
row source to a statement like

SELECT DISTINCT status FROM MyTable

Then, you can create the filter in one step by putting the following in
that combo box AfterUpdate event:

Me.Filter = "status = '" & Me.MyComboBox & "'"

To get rid of Null values, try:

Me.Filter = "IsNull(status) = False" or
Me.Filter = "Not Is Null status"

although I would prefer to filter the null values in
Click to expand...
the
form's record
source beforehand.
Good luck,
Pavel

Sammy wrote:

Beautiful! Thanks so much!

I have a follow-up question to your previous reply. The
code you provided me turned the filter on and off:
Me.Filter = "completed"
Me.FilterOn = Not Me.FilterOn

I would like to create a filter using buttons on other
forms where the values in the field are not yes/no. For
example, my field is called "status" and the values can
be "complete" "Partial" "incomplete" and null. What
would the filter command be to filter the "partial"
records? What would be the command to filter null
records?
Thanks.
-----Original Message-----
Try this in the button's Click event:

if Me.Filter = "Completed" then _
Me.Filter = "Completed = 0" else _
Me.Filter = "Completed"
Me.FilterOn = True

Good luck,
Pavel


Sammy wrote:

Thanks. This is a start. Clicking my button once
turns
the filter completed=yes on. Clicking it a
Click to expand...
second
time
takes the filter off, so I see completed=yes and
completed=no. Is it possible for the second
Click to expand...
click
to
change the filter from completed=yes to completed=no?
I
want to be able to see either all completed
Click to expand...
records
or
all incompleted records. I really don't need to see
both
at the same time.

-----Original Message-----
Try this in the button's Click event:

Me.Filter = "Completed"
Me.FilterOn = Not Me.FilterOn

Good luck,
Pavel

Sammy wrote:

What's the code to create a command button which
toggles
a filter by the "completed" field, which is a yes/no
value? So click the button to view all
Click to expand...
records
with
completed value = yes, and click again to view
completed
value = no. Thanks!
.

.

.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top