Latest Combo box question

[Eric G]

[Sorry if you read this recently, but I can't find my post from
yesterday. I changed the title to help things out.]

I have a dropdown combo box on a form that displays the nine values of
a table.
Would someone be able to tell me how I can set up the combo box so
that it only displays the first eight of the nine values as choices?

COMBO BOX:
Control source: ReasonID
Row source: SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM
Reasons;
Column widths: 0";0.7"

TIA Eric

 

[Ken Snell]

Change the Row Source query to exclude the record whose value of ReasonID
you don't want:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> #

Change # to the number that you don't want to be in the dropdown list.

 

[Eric G]

Thanks Ken,

That was nice and easy to set up.

Eric

Change the Row Source query to exclude the record whose value of ReasonID
you don't want:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> #

Change # to the number that you don't want to be in the dropdown list.

--
Ken Snell
<MS ACCESS MVP>

[Sorry if you read this recently, but I can't find my post from
yesterday. I changed the title to help things out.]

I have a dropdown combo box on a form that displays the nine values of
a table.
Would someone be able to tell me how I can set up the combo box so
that it only displays the first eight of the nine values as choices?

COMBO BOX:
Control source: ReasonID
Row source: SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM
Reasons;
Column widths: 0";0.7"

TIA Eric

 

[Eric G]

Hi Ken,

Change the Row Source query to exclude the record whose value of ReasonID
you don't want:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> #

Change # to the number that you don't want to be in the dropdown list.

I'd also like to have two more ReasonID values not showing in the
dropdown list.
I tried the following code but it didn't work:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> 9 AND WHERE
[Reasons].[ReasonID] <> 2 AND WHERE
[Reasons].[ReasonID] <> 3;

It actually removed all choices from the drop-down list and didn't
work at all.
I'd appreciate a tip here.

TIA Eric

 

[Ron Weiner]

Try

SELECT ReasonID, Reason FROM Reasons WHERE ReasonID NOT IN(9,3,2)

This assumes that the reasonID is a Numeric value. If Reason is a String
then

SELECT ReasonID, Reason FROM Reasons WHERE ReasonID NOT IN('9','3','2')

You might want to add an order by to get a reasonable sort order for the
combo

ORDER BY Reason


Ron W

Hi Ken,

Change the Row Source query to exclude the record whose value of ReasonID
you don't want:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> #

Change # to the number that you don't want to be in the dropdown list.

I'd also like to have two more ReasonID values not showing in the
dropdown list.
I tried the following code but it didn't work:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> 9 AND WHERE
[Reasons].[ReasonID] <> 2 AND WHERE
[Reasons].[ReasonID] <> 3;

It actually removed all choices from the drop-down list and didn't
work at all.
I'd appreciate a tip here.

TIA Eric

 

[Eric G]

Hi Ron,

Thanks very much! Your code worked out perfectly.

Eric

 

[Ken Snell]

Sorry - I missed you post last night!

In addition to Ron's solution, you can use your original approach by
removing the "extra" WHERE words:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> 9 AND
[Reasons].[ReasonID] <> 2 AND
[Reasons].[ReasonID] <> 3;


--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Change the Row Source query to exclude the record whose value of ReasonID
you don't want:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> #

Change # to the number that you don't want to be in the dropdown list.

I'd also like to have two more ReasonID values not showing in the
dropdown list.
I tried the following code but it didn't work:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> 9 AND WHERE
[Reasons].[ReasonID] <> 2 AND WHERE
[Reasons].[ReasonID] <> 3;

It actually removed all choices from the drop-down list and didn't
work at all.
I'd appreciate a tip here.

TIA Eric

 

[Eric G]

"Ken Snell" <[email protected]> wrote:

Sorry - I missed you post last night!

In addition to Ron's solution, you can use your original approach by
removing the "extra" WHERE words:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> 9 AND
[Reasons].[ReasonID] <> 2 AND
[Reasons].[ReasonID] <> 3;

Thanks Ken.
I thought about playing around a bit with the Wheres and such.
I'll make a note about this for the future.
Best,
Eric