I’m used to using this syntax when employing filter tables to forms in HSQLDB:
SELECT
*
FROM
"tbl_Users"
WHERE
((UPPER("FirstName") LIKE '%' || UPPER (:Var_FN) || '%' ) OR (:Var_FN IS NULL))
I cannot get this to work in FireBird, and it seems to have to do with the second part in the WHERE clasue: “OR (:Var_FN IS NULL))”
What’s the alternative? I need a blank field to select all records from the filtered table.
I think the user parameter value must be explicitly CAST as VARCHAR: e.g.
WHERE
((UPPER("FirstName") LIKE '%' || UPPER (CAST(:Var_FN) AS VARCHAR(10)) || '%' )
OR
(CAST(:Var_FN) AS VARCHAR(10) IS NULL))
UPDATE: Below-attached is a sample FB Base file to show a query similar to yours using the above code-technique. To test just run the query qInput_via_user_parameter_UPPERCASE with just ONE upper or lower-case alphabetic letters. Not sure why the above-quoted code didn’t work, but let us know if you have success (or not) with this approach anyway.
I tried this a bit quickly, but couldn’t make it work. Using LO Base 6.4 on Windows 10.
Your original code works fine with a minor change (also only need to cast for NULL):
WHERE
"FirstName" LIKE '%' || :Var_FN || '%'
OR
CAST((:Var_FN) AS VARCHAR(1)) IS NULLIt looks I was too hasty the first time around, and made some syntax error. I can now get the user input via parameter to work on my own file 
Filtering directly from the filter table looks much cleaner to my eyes, but I cannot make it work for NULL. Doesn’t matter, I’ll go with user parameters in my own work.
with just ONE upper or lower-case alphabetic letters.
Not sure what you mean by this, it works with one or many or a blank field.
Hello,
Please see my answer and comments in this post:
Thank you Ratslinger.
While this solution works, it seems I have to type as many characters into COALESCE as the user might conceivably wish to use for his search term? Considering I have VARCHAR(100) and the user might want to search an arbitrary length of that field - this seems extremely clunky relative to “OR * IS NULL.”
Is there something I’m not understanding correctly?
No, you have understood it correctly. What you may not have understood is that the situation is a bug (don’t believe it has been reported from the linked post) and the solution is simply a work around.
You example defines a first name and it seems 100 characters is overkill for this. So even if the field is 100 characters, if entry does not exceed 25, then only 25 are needed.
I have in several tables a “Description” field, which in HSQLDB is MEMO, and I think I’ll convert to VARCHAR(100). But I need to be able to search it properly. If this is a workaround, I can certainly live with having 20 or 30 characters max until the bug gets fixed some time in the future.
Thanks for the explanation.