I have a table tFilters that stores the last filter criteria a person uses. One filter is to filter the main table to a specific district.
tFilters stores the name DistrictFilterName and status of the checkbox DistrictFilterOn that activates/deactivates the filter.
In the query qMainTable I put the following criteria under the field District:
Filter: IIF([tFilters].[DistrictFilterOn], [tFilters].[DistrictFilterName], "" )
This is giving me an error message that there is a misplaced "." or "!" or a parenthesis after a null statement.
The "Filter:" is required instead of "=" for IIF criteria according to MS. If DistrictFilterOn evaluates true, the criteria is the stored name, otherwise the criteria should be blank to select everything.
A quick search on the subject hasn't given me any help.
1. The table tFilters must be present in the query (see attachment). This means that it must be listed in the FROM part of the SQL expression (it also could be in a subquery).
2. Depending on the regional settings in the Windows Control Pannel, the list separator could be a semicolon ";" instead of a comma ",". This is only true for the query builder, in SQL it is always a comma.
3. With a different table (Tbl_CF_Data in my example), the QSL expression of the query looks like:
, IIF( tFilters.DistrictFilterOn=True, tFilters.DistrictFilterName ,'') AS Filter
FROM Tbl_CF_DATA, tFilters;
tFilters was present in the query. I've never used the "AS Filter" construct, I'll have to do a little studying to get my arms around it. I normally just use a WHERE clause but this is the first time I've had to do an IF in a WHERE clause.
My biggest problem is that there are more than 30 fields being SELECTed. As many of 10 of them will need this type of optional criteria. I am looking to keep this from becoming a brain-fryingly complex SQL specification.
To understand this construct, in your example, since DistrictName is one of the fields, don't I need to also include it in the select first and then again as the filter?
SELECT ... <other fields>
, IIF( tFilters.DistrictFilterOn=true, tFilters.DistrictFilterName=tData.DistrictName, ' ' ) AS Filter
FROM tData, tFilters;
I've just been doing a search on my problem and most are saying to use a CASE construct. However, there are a couple of posts, advising against using CASE because the entire statement must be evaluated before a decision is made. They recommend using simple Boolean statements.
Looking at my problem, the WHERE clause can be reduced to a simple OR. If the DistrictFilterOn is not true - select everything OR if the DistrictName=DistrictNameFilter then select only those records.
WHERE (DistrictFilterOn=False) OR (DistrictName=DistrictNameFilter)
If that works it is beautifully simple! And I can use the Query Builder by just putting those 2 clauses in the Criteria and Or lines for each field that needs a filter.
What do you think? I'll let you know next week after I get a chance to try it.