Combo Box List

[Jason]

We would like to allow user to select the items by using a combo box.

Someone has suggested us to use the following query for the data source:

SELECT DISTINCT ProductCategoryKey AS CategoryKey, ProductCategoryName AS
Category
FROM ProductCategory
UNION
SELECT - 1, 'All Categories'

However, I would like to know why we are able to select all items when the
CategoryKey = -1 ? If this query is not correct, what query should I use ?

Thank you for your advice.

 

[JohnFol]

This will allow you to select the word "AllCategories" with an associated
value of -1. It sounds like the query that uses the combo as the criteria
does something like

"Select * from Items where (ProductCategoryKey = Forms!FormName!Combo) or
(Forms!FormName!Combo = -1)

 

[Van T. Dinh]

I guess you should ask the person who suggested it at the first place???

The idea is similar to the Access Web article:

http://www.mvps.org/access/forms/frm0043.htm

The important part is to use it correctly when the user select 1 of the
possible choices including the "All Categories".

 

[Jason]

Dear Van,

It is exactly what you are referring to.

On the other hand, I would like to know why the NULL value can retrieve all
records ?

Thanking you in anticipation.

Jason

 

[Van T. Dinh]

JohnFol already answered this, actually.

It doesn't need to be Null as you have already seen two: -1 and Null. All
you need is that this value is *different* from all the other possible
values.

The important thing is to set up the (following) Query / SQL String
correctly when you use the value of the ComboBox which JohnFol already gave
the sample SQL. Note carefully that the LHS of the 2 Boolean expressions
joined by OR are *different*: one refers to your Table Field and the other
refers to the (value of) the ComboBox.