Combo Box List

  • Thread starter Thread starter Jason
  • Start date Start date
J

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.
 
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)
 
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
 
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.
 
Back
Top