Using criteria to create a dialog box to type search terms

[theunfinished]

Hi guys. My first post here.

I have a database for my music projects. The table is a list of sound names I've created, plus a description field with a number of different description terms in it.

I'm trying to make a query so that I can type in two or three search terms into a dialog box and it will bring up any sounds that have these search terms in the description field.

In Microsoft Access I would type the following into the criteria field of the description column in the query design page: Like "*" & [Term 1] & "*" And Like "*" & [Term 2] & "*" And Like "*" & [Term 3] & "*"

This code doesn't work in Base, so what would I need to enter to get the same result?

Any help would be great.

Many thanks,
Matt

[Sliderule]

Matt:

Welcome to OpenOffice.

I will assume, since you did not say, that you are working with the 'default' embedded database engine HSQL rather than being 'linked' to some other database engine ( such as, MySQL, SQLite, PostGRE, MS Access, dBase, Paradox, Oracle etc ), and, you can confirm this by looking at the status line, at the bottom, if it says: HSQL database engine then what I have written below applies.

Now, the answer also, depends on which version of OpenOffice you are using

1. With OpenOffice Version 3.0.1 AND prior releases:
   
   the notation to indicate a 'prior' or 'post' entry as you have asked in Microsoft Access ( "*" ) canNOT be 'concatenated' with the prompt. But, a work-around is to have the user INCLUDE it in the response. And, with HSQL, the code is the literal, a percent sign: % .
   
   So, you could enter multiple prompts, each on an Or line. Each Parameter Prompt begins with a colon : , and is one word - no spaces. Therefore, if you were to write it with SQL, it would look like this:
   

   Code:

   Like :Enter_Pct_String1_Pct OR Like :Enter_Pct_String2_Pct OR Like :Enter_Pct_String3_Pct

   
   
2. As of OpenOffice 3.1( now available for download ) . . . it will allow you accomplish the task with the following code:
   

   Code:

   Like '%' || :Enter_Search_String1 || '%' OR Like '%' || :Enter_Search_String2 || '%' OR Like '%' || :Enter_Search_String3 || '%'

   
   Note in the above, a LITERAL is enclosed with SINGLE QUOTES, so, use '%' , and, two pipes are used for concatenation ||

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.