Parameter querry question

[jharris]

I've worked with MS Access in the past and am trying OO Base.
I want to retrieve records in a query using a parameter query based on a Date field. The retrieved records need to be based on a date range that will almost always be different.
For Example: Between 6/15/2007 And 7/5/2007.
Another example: Between 8/2/2007 and 8/9/2007.

Any suggestions?

[Sliderule]

jHarris:

You can create a Parameter Query in Base. According to Base Help file:

[jharris]

Worked like a charm!

[zeddock]

In the past I have used MS-Access query to bring up a question dialog for information inputs. Here was one:

[Sliderule]

zeddock:

That is a good question. Please see the link below . . . the an example and explanation:

http://user.services.openoffice.org/en/forum/viewtopic.php?f=61&t=11384&p=53291

In words . . . the user must input the parameter % as part of the response. You can prompt . . . the user to input it as in the example above . . . but . . . at least as of now, to the best of my knowledge, it cannot be done 'automatically / programatically'.

Please let me / us know if this helps.

Sliderule

[zeddock]

Thank you for trying to help.

For my situation, this does not help. Not sure how to handle this situation for older MS-Access user to migrate to OOo.

Thanx.

zeddock

[Sliderule]

zeddock:

[zeddock]

Sad yes. But you prolly know it can be true, too. In this case it happens to be an older lady.

zeddock

[Sliderule]

Just as an FYI ( For Your Information ) -- with OpenOffice 3.1 ( 2009-05-07 ) . . . an important new 'feature' is:

[andrewtc]

I am using Open office 3.0, after using your code

BETWEEN :From_Date AND :To_Date
LIKE '%' || :Enter_part_of_the_description || '%'

the querry table displayed no data! Shall I upgrade to a higher version of Open Office or there are another code that I can use for querry data?

Thanks a lot.

[Sliderule]

Please re-read my post . . . since you are using OpenOffice 3.0 rather than OpenOffice 3.1

Additionally, IF your HSQL ( assuming you are using the 'default' embedded database engine - HSQL - rather than another database engine such as MySQL, PostGRE, SQLite etc ) - - - if the column is defined as VARCHAR rather than VARCHAR_IGNORECASE what is entered by the user must be a 'match' including Case - UPPER / lower case.

For example, if your column contains the data 'Ontario, Canada' as a VARCHAR the following will NOT be found since it does NOT also match the case: LIKE '%canad%'

BUT, if you have defined the column as VARCHAR_IGNORECASE then LIKE '%canad%' will be found.

Sliderule

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

[andrewtc]

I am now using Open Office Version3 (Traditional Chinese), when I added '%' and || to the code, there're nothing appear to my search, However, when I just type : and description, it works.
Thank you very much.

[guachinver]

I have just updated to 3.1 version then I try a query with parameter and placeholder.
SELECT * FROM `miag` WHERE `Name` LIKE '%' || :Enter_part_of_the_description || '%'

This LIKE is not working, some idea?

The language is Spanish, may be a reason for this error?. Of course, the query without the placeholders concatenation work perfectly when I introduce in popup windows %a%.

The error message:

Estado SQL: 3075
Código de error: -2147217900

Uso de barras verticales no válido en la expresión de consulta '`Name` LIKE '%' || :Enter_part_of_the_description || '%''.
-----------------------------------------------------------------------------------------------------


My (bad) translation:
SQL status: 3075
error:code -2147217900
The double pipe is not valid on query '`Name` LIKE '%' || :Enter_part_of_the_description || '%''.

[Sliderule]

guachinver:

You wrote that your SQL is:

[guachinver]