Ask Your Question

Base: Conditional Query. How to filter with a ListBox and add ALL?

asked 2016-12-01 23:36:30 +0100

Kokoliso gravatar image

Hello. I am trying to make a Filter in a Form with a ListBox of "Workers" and I get success filtering when selecting a worker and refreshing a form. But I would like to show all the workers when selecting something like a "All workers" item. In the Listbox I can see the list of all the workers and the first value is something like a "blank" or NULL or EMPTY ( I am not sure. I can set this in the cell where it is stored the data). Then, I was thinking that when this "NULL" value is stored in the filter cell, then would be great that when I run a query that doesn't filter by worker, something like a condition after the "WHERE" statement. I have been searching and in HSQL docs says something about CASE or CASE WHEN, but when I try to run a query doesn't recognize CASE. Something like this:

SELECT "Worker_ID" AS "SelectedWorker" FROM "Filters" WHERE "Filter_ID" = '0' CASE "Worker_ID" IS NOT NULL THEN SELECT * FROM "Assets" WHERE "Worker_ID" = "SelectedWorker"; ELSE SELECT * FROM "Assets"; END CASE

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-12-02 00:46:35 +0100

Ratslinger gravatar image

A couple of items. When checking for null use IS NULL. Second, you are over thinking the solution. I've attached a sample DB to demonstrate what you are looking for. The SQL Query looks like this:

SELECT "Bike".* FROM "Bike", "FILTER" WHERE  ( ("Bike"."Id_Bike" = "FILTER"."BikeID" ) OR ( "FILTER"."BikeID" IS NULL ) )

So if the blank entry entry in the listbox is selected, all table entries will be listed.

Sample: BikeDemoFilterAllOrOne.odb

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-12-01 23:36:30 +0100

Seen: 406 times

Last updated: Dec 02 '16