Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Hello once again,

There is a method to do this but it will require you to change your approach to filtering. It again relates to the last question and answer I provided so hopefully this sample will work for you. I will be using the CompanyCallingRevised form which is a modified version of yours and further modified for this answer. I am not using your current specified form because, once again, it is confusing and after spending too much time trying to figure out what results you are expecting I gave up. Please, in the future, include what the expected results are to be. Your form sample has no labels and someone looking at this must edit the form & try to figure out everything from scratch.

While the filtering method you are using works, it is extremely restrictive. You cannot use wildcards such as

Description LIKE( CONCAT( '%', "FILTER"."DESCRIPTION", '%' )

which allows you to select something if a portion matches. In your case, you want to select ALL of something if the filter field is empty or IS NULL.

So, in your filtering you have a select statement of everything. Then to weed out the unwanted, the 'Master' & 'SubForm' tables are linked via the form property link values. For more flexible filtering, eliminate these field properties completely and base the result entirely on the SQL statement.

My attached form (as was done in previous question) does just that. It incorporates a WHERE selection based on selected info vs filter field value. In the SQL WHERE statement, I use WHERE table.value = filter.value for selection. This gives you only records matching the filter field. Here I add another check if nothing is selected WHERE (table.value = filter.value) OR (filter.value ISNULL).

Here is the complete SQL statement used in the sample:

SELECT "companies"."name", "companies"."country_id",
       "companies"."seta_id", "A"."sector_id",
       "A"."notes", "companies"."company_id"
FROM "companies", "Filter"
  LEFT JOIN (SELECT "company_id", "sector_id", "notes"
             FROM "company_sector", "Filter"
             WHERE "sector_id" = "Integer") A
                   ON "companies"."company_id" = "A"."company_id"
WHERE (("companies"."country_id" = "Filter"."country_id")
           OR ("Filter"."country_id" IS NULL))
      AND (("A"."sector_id" = "Filter"."Integer")
           OR ("Filter"."Integer" IS NULL))

Please note: not all SQL can be created using the graphic interface. In this case it is in SQL View mode (my preferred method).

Now to use the sample, you can select an item from each list box and get the specific result. If, however, you want ALL items (your wildcard requirement) from either or both of the list boxes, for that selection simply select the BLANK line at the very beginning of the list. This will be placed in the filter field & the SQL will detect as NULL.

Sample: WildCardListBox