Require help with Option Group

[Tom]

Experts,

I have an option group on a form that uses values "1" and "2". Their (more
meaningful) labels are "Yes" (1) and "No" (2); the values are stored in
[tblIssues ].[WatchlistPAR].

Now, I have added an unbound combo box by which I want to filter for "All",
"Yes", and "No" records. I used the following SQL for the combo's
(filter) rowsource:

SELECT DISTINCT WatchlistPAR FROM tblIssues UNION SELECT "<All>" FROM
tblIssues;

Now, as you probably have already determined, my filter combo shows me
"All", "1", "2" (given the SQL above). Although the filter works (using
1, 2) fine for those 3 filter criteria (I get proper record count), I'm not
too happy with the values of "1" and "2". They don't mean anything to a
user.

Hence, I translated my SQL into the following:

SELECT DISTINCT IIf([WatchlistPAR]='1','Watchlist','Yes') AS WatchlistPARExp
FROM tblIssues UNION SELECT "<All>" FROM tblIssues;

While I now show "All", "Yes", "No" in my filter combo, I don't pull the
proper record count. Logically, "1" <> "Yes"... etc.


Below is the SQL for applying the filter:

DoCmd.ApplyFilter , "[WatchlistPAR] =
Forms![frmIssuesOpen]![cboWatchlistPAR]"

How do I modify the DoCmd.ApplyFilter so that "Yes" gives me all records =
"1" and "No" gives me those records = "2"?

I'd really appreciate any help on this.

Thanks,
EEH


BTW, I have added a hidden textbox that "translates" the "1" into "Yes" and
"2" into "No". I also modified my DoCmd.ApplyFilter to cross-reference
between the filter combo and the hidden textbox. I thought that this
solution maybe a work-around; however, it doesn't seem to match the records
either.

 

[John Welch]

Tom, here are some ideas. Forgive me if I suggest too many alternatives to
what you are doing: it's late and I'm not super coherent. But hopefully this
ranting will give you some useful ideas.

You could use a 2 column combo box, with 2 rows like this:
1 "Yes"
2 "No"
and then set the width of the first column to zero so the user only sees the
yes and no.
But if your only values are yes and no, then why not make the field be a
Yes/No field instead of text? Then Yes or No will be displayed (if you set
the field's format to yes/no) but will actually store values of true and
false instead of just text. And why not just put Yes and No directly into
the combo box instead of using the select distinct (what happens if there
are no records in the table that have "no", for example?)

Better yet, if your only options are Yes and No, why not use a checkbox
whose values will be yes and no. (or true and false) instead of the option
group?

You could modify your filter statement like this:

If not isnull(me.cboWatchlistPAR) then
DoCmd.ApplyFilter , "[WatchlistPAR] = " & me.cboWatchlistPAR
end if

that way, you don't even have to put the "all" in there, you can just leave
it blank if you don't want to filter on it. But if you really want to have
it say "all", you can add another row to the combo box: (bound column is 1,
column 1 width set to zero)
(null) "all"
true "yes"
false "No"

and use the same lines above to set the filter

hope this helps
-John

Experts,

I have an option group on a form that uses values "1" and "2". Their
(more meaningful) labels are "Yes" (1) and "No" (2); the values are stored
in [tblIssues ].[WatchlistPAR].

Now, I have added an unbound combo box by which I want to filter for
"All", "Yes", and "No" records. I used the following SQL for the
combo's (filter) rowsource:

SELECT DISTINCT WatchlistPAR FROM tblIssues UNION SELECT "<All>" FROM
tblIssues;

Now, as you probably have already determined, my filter combo shows me
"All", "1", "2" (given the SQL above). Although the filter works (using
1, 2) fine for those 3 filter criteria (I get proper record count), I'm
not too happy with the values of "1" and "2". They don't mean anything
to a user.

Hence, I translated my SQL into the following:

SELECT DISTINCT IIf([WatchlistPAR]='1','Watchlist','Yes') AS
WatchlistPARExp FROM tblIssues UNION SELECT "<All>" FROM tblIssues;

While I now show "All", "Yes", "No" in my filter combo, I don't pull the
proper record count. Logically, "1" <> "Yes"... etc.


Below is the SQL for applying the filter:

DoCmd.ApplyFilter , "[WatchlistPAR] =
Forms![frmIssuesOpen]![cboWatchlistPAR]"

How do I modify the DoCmd.ApplyFilter so that "Yes" gives me all records =
"1" and "No" gives me those records = "2"?

I'd really appreciate any help on this.

Thanks,
EEH


BTW, I have added a hidden textbox that "translates" the "1" into "Yes"
and "2" into "No". I also modified my DoCmd.ApplyFilter to
cross-reference between the filter combo and the hidden textbox. I
thought that this solution maybe a work-around; however, it doesn't seem
to match the records either.

 

[Tom]

John,

I'll give it a try a work... will let you know the outcome later on.

Thanks,
EEH

Tom, here are some ideas. Forgive me if I suggest too many alternatives to
what you are doing: it's late and I'm not super coherent. But hopefully
this ranting will give you some useful ideas.

You could use a 2 column combo box, with 2 rows like this:
1 "Yes"
2 "No"
and then set the width of the first column to zero so the user only sees
the yes and no.
But if your only values are yes and no, then why not make the field be a
Yes/No field instead of text? Then Yes or No will be displayed (if you set
the field's format to yes/no) but will actually store values of true and
false instead of just text. And why not just put Yes and No directly into
the combo box instead of using the select distinct (what happens if there
are no records in the table that have "no", for example?)

Better yet, if your only options are Yes and No, why not use a checkbox
whose values will be yes and no. (or true and false) instead of the option
group?

You could modify your filter statement like this:

If not isnull(me.cboWatchlistPAR) then
DoCmd.ApplyFilter , "[WatchlistPAR] = " & me.cboWatchlistPAR
end if

that way, you don't even have to put the "all" in there, you can just
leave it blank if you don't want to filter on it. But if you really want
to have it say "all", you can add another row to the combo box: (bound
column is 1, column 1 width set to zero)
(null) "all"
true "yes"
false "No"

and use the same lines above to set the filter

hope this helps
-John

Experts,

I have an option group on a form that uses values "1" and "2". Their
(more meaningful) labels are "Yes" (1) and "No" (2); the values are
stored in [tblIssues ].[WatchlistPAR].

Now, I have added an unbound combo box by which I want to filter for
"All", "Yes", and "No" records. I used the following SQL for the
combo's (filter) rowsource:

SELECT DISTINCT WatchlistPAR FROM tblIssues UNION SELECT "<All>" FROM
tblIssues;

Now, as you probably have already determined, my filter combo shows me
"All", "1", "2" (given the SQL above). Although the filter works
(using 1, 2) fine for those 3 filter criteria (I get proper record
count), I'm not too happy with the values of "1" and "2". They don't
mean anything to a user.

Hence, I translated my SQL into the following:

SELECT DISTINCT IIf([WatchlistPAR]='1','Watchlist','Yes') AS
WatchlistPARExp FROM tblIssues UNION SELECT "<All>" FROM tblIssues;

While I now show "All", "Yes", "No" in my filter combo, I don't pull the
proper record count. Logically, "1" <> "Yes"... etc.


Below is the SQL for applying the filter:

DoCmd.ApplyFilter , "[WatchlistPAR] =
Forms![frmIssuesOpen]![cboWatchlistPAR]"

How do I modify the DoCmd.ApplyFilter so that "Yes" gives me all records
= "1" and "No" gives me those records = "2"?

I'd really appreciate any help on this.

Thanks,
EEH


BTW, I have added a hidden textbox that "translates" the "1" into "Yes"
and "2" into "No". I also modified my DoCmd.ApplyFilter to
cross-reference between the filter combo and the hidden textbox. I
thought that this solution maybe a work-around; however, it doesn't seem
to match the records either.