Using wildcards in parameter query to create filter

Discuss the database features

Using wildcards in parameter query to create filter

Postby SubmarineDeckhand » 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.

openoffice 3.4 ubuntustudio12.04LTS
Posts: 2
Joined: Tue Dec 25, 2012 6:16 am

Re: Using wildcards in parameter query to create filter

Postby DACM » Tue Dec 25, 2012 1:39 pm

Welcome SubmarineDeckhand,

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

Hey that syntax looks 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   Expand viewCollapse view
     STREET || ', ' || CITY || ', ' || STATE AS ADDRESS,
     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   Expand viewCollapse view
     "lastname" || ', ' || "firstname" AS FULLNAME,
     "street" || ', ' || "city" || ', ' || "state" AS ADDRESS,
     "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:
Modified to search on CONCAT field
(75.84 KiB) Downloaded 279 times
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

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

Postby SubmarineDeckhand » 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.
openoffice 3.4 ubuntustudio12.04LTS
Posts: 2
Joined: Tue Dec 25, 2012 6:16 am

Return to Base

Who is online

Users browsing this forum: No registered users and 6 guests