Apache OpenOffice Community Forum

Posted: **Tue Dec 25, 2012 6:33 am**

Is there any way I can specify in a query design such as follows:

add a calculated field in the query grid that is named alias "ffilter" of an address book as example where the calculated field above is lastname&firstname&address1&city&postalcode&phonenmbr meaning all text fields are concatenated into one long string. The criteria to be specified using LIKE, GLOB, etc and wild cards such that it is LIKE "*"&=['enter whole or partial text string']&"*" such that the parameter or variable name is the actual displayed instructions within the single quotes in the square brackets.

So when the query is run, it will open a parameter dialog box with label instructing the user to type in a whole known or partial text string that they are searching for. If one typed in just a portion of a last name say, then the query returns all records with that string embedded in any last name. Same would occur if someone just input a zipcode or postal code, although a zipcode that was close to a partial telephone number might also return an extra record, but examination of the data returned would likely present the desired record anyway.

I saw a post where HSQL and SQLite are somewhere in limbo and their syntaxes for using this functionality are somewhat different but accomplish similar results, however the use of wildcards like % or * seem somewhat restricted in this application or use.

Thanks

Posted: **Tue Dec 25, 2012 1:39 pm**

Welcome SubmarineDeckhand,

SubmarineDeckhand wrote:LIKE "*"&=['enter whole or partial text string']&"*"

Hey that syntax looks familiar...is that from MS Access?

Parameter queries in Base do not require support in the chosen back-end database engine (aka. HSQL, SQLite, etc.). That's because the Base query-parser supports parameter queries in the front-end, including user-input processing (popup input box), before sending the final query to the back-end database engine. The Base syntax utilizes the colon (:) character to designate a parameter (:filter), as shown below. The parameter query name is also used to instruct the user or to simply differentiate among multiple parameters in the same query. Base imposes other restrictions on parameter queries such as the parameter_name cannot include spaces, so you will need to utilize underscores (:enter_whole_or_partial_text_string).

So the Parameter Query syntax becomes:

Code: Select all

SELECT 
     ID, 
     LASTNAME || ', ' || FIRSTNAME AS FULLNAME, 
     STREET || ', ' || CITY || ', ' || STATE AS ADDRESS, 
     ZIP, 
     PHONE 
     FROM STUDENTS 
     WHERE ( UPPER ( FULLNAME || ADDRESS || ZIP || PHONE ) 
     LIKE '%' || UPPER ( :filter ) || '%' OR :filter IS NULL )

Or if you originally created the table's column names through the Base GUI using any lowercase letters, then you've (perhaps inadvertently) created case-sensitive names which will now require double-quotes per the ANSI SQL standards as follows:

Code: Select all

SELECT 
     ID, 
     "lastname" || ', ' || "firstname" AS FULLNAME, 
     "street" || ', ' || "city" || ', ' || "state" AS ADDRESS, 
     "zip", 
     "phone" FROM STUDENTS 
     WHERE ( UPPER ( FULLNAME || ADDRESS || "zip" || "phone" ) 
     LIKE '%' || UPPER ( :enter_whole_or_partial_text_string ) || '%' OR :enter_whole_or_partial_text_string IS NULL )

Take a look at the stored Query named "Concat query" in the example database below to see this parameter query in action.

Now, you can also use Forms to process and display parameter queries using a permanent input box(es). Attached below is a modified version of this example: [Example #3] Filter/Search with Forms (leveraging SubForms).

...specifically, take a look at the Form named "5 Text Box Search Concat Field" which implements your contact search across CONCATENATED fields as you've specified:

Posted: **Tue Jan 15, 2013 9:48 pm**

Thanx for taking the time to answer. I will give these a close look shortly. Yes you are correct syntax in my example was MSaccess. used to use it quite a bit in a previous work life.

Apache OpenOffice Community Forum

Using wildcards in parameter query to create filter

Using wildcards in parameter query to create filter

Re: Using wildcards in parameter query to create filter

Re: [Example #1] Filter/Search with Forms (leveraging SubFor