Hi all,
Ok, one can show a selection or of a listbox where all is basically a blank in the filter table. Just curious if it is at all possible to show records where the field actually is null ?
Thank you for your thoughts.
Hello,
Not quite certain what you are asking. Checking a NULL in a filter table or in a field is the same. See SQL and sample in this post → Base: Conditional Query. How to filter with a ListBox and add ALL?
Your SQL just needs :
WHERE "My_Field" IS NULL
Edit:
Based on further explanation in comment, you can do this with a second added control. You original list box remains as is. The second just needs to be a true/false condition in another field in the Filter Table. In my testing I used an on/off check box - here named SelAll.
With this SQL statement, where the condition is in a field named Fname there are three choices:
SELECT "Table".* FROM "Table", "FILTER" WHERE
("FILTER"."SelAll" = TRUE AND "FILTER"."Fname" IS NULL) OR
("FILTER"."SelAll" = FALSE AND "FILTER"."Fname" IS NULL AND "Table"."Fname" IS NULL) OR
(NOT "FILTER"."Fname" IS NULL AND "FILTER"."Fname" = "Table"."Fname")
If SelAll is true & Fname is null, all records are selected.
If SelAll is false & Fname is null, only records with NULL Fname are selected.
If SelAll is true OR false & Fname is NOT null, only records equal to Fname are selected.
This was tested with embedded HSQL and should work with v2.5 as you requested.
Well, sory for being not clear, in a listbox I want to show options for display all, or selected choice or only those records where the field is blank, so I might have Peter, Paul and Mary showing up as all, only Peter if Peter is selected or only a blank where Mary has no fistname entry.
Hope that makes sense.
I somehow think the sql for the listbox would need some sort of union statement
Yes, a much better explanation. Can easily see doing this with a macro (have done similar and more complex for personal use). Would like to try and do this with just SQL but you have not stated what database you are using. This is important in this situation.
@Ratlinger
Thank you very much, you are a rel gem!