OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Parameter querry question
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
jharris
Newbie
Newbie


Joined: 17 Aug 2007
Posts: 2

PostPosted: Fri Aug 17, 2007 12:52 pm    Post subject: Parameter querry question Reply with quote

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?
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Aug 17, 2007 1:32 pm    Post subject: Reply with quote

jHarris:

You can create a Parameter Query in Base. According to Base Help file:
parameters;queries (Base) wrote:
Parameter queries

You must place the variable between square brackets (=[x]) to create a query with variable parameters. Alternatively, you can use an equal sign followed by a colon (=:x). When the query is executed, the program will display a dialog asking you for the expression to which the variable x should be assigned.

If you query several parameters at the same time, you will see a list field in the dialog containing all of the parameters and an input line alongside each one. Enter the values, preferably from top to bottom, and press the Enter key after each line.


In your case, because you want it to be created in a "date range" . . . this may be done as illustrated in the graphic below.

NOTE: the date is 'prompted' with:
  • >= :From_Date
  • <= :To_Date

so the user my input two dates, and, the query will retrieve the desired records. In the SECOND test_date . . . the Visible check box is UNCHECKED so test_date will only be displayed once in the result set.



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

Sliderule
Back to top
View user's profile Send private message
jharris
Newbie
Newbie


Joined: 17 Aug 2007
Posts: 2

PostPosted: Fri Aug 17, 2007 1:48 pm    Post subject: Thank You Reply with quote

Worked like a charm!
Back to top
View user's profile Send private message
zeddock
General User
General User


Joined: 08 Jun 2007
Posts: 7

PostPosted: Sat Nov 29, 2008 12:27 pm    Post subject: Reply with quote

In the past I have used MS-Access query to bring up a question dialog for information inputs. Here was one:
Code:
Like "*" & [enter part of the description] & "*"


So in OpenOffice BASE, how do I accomplish the same?

Using
Code:
'LIKE  :Enter_part_of_the_description

does not get it as wildcard do not seem to be available in this situation.

Thanx!
zeddock
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sat Nov 29, 2008 12:42 pm    Post subject: Reply with quote

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'. Crying or Very sad

Please let me / us know if this helps.

Sliderule
Back to top
View user's profile Send private message
zeddock
General User
General User


Joined: 08 Jun 2007
Posts: 7

PostPosted: Sat Nov 29, 2008 1:07 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sat Nov 29, 2008 2:08 pm    Post subject: Reply with quote

zeddock:

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

How sad Crying or Very sad that an individual is incapable of learning something different.

Sliderule
Back to top
View user's profile Send private message
zeddock
General User
General User


Joined: 08 Jun 2007
Posts: 7

PostPosted: Sat Nov 29, 2008 4:37 pm    Post subject: Reply with quote

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

zeddock
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu May 07, 2009 7:59 pm    Post subject: Reply with quote

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

According to: http://wiki.services.openoffice.org/wiki/Base/New_features_in_3_1 wrote:

Parameters recognized in function argument lists

OOo's parser has the ability to recognize parameters in function argument lists.


Therefore, with zeddock's example above, beginning with OpenOffice 3.1, the following code will work, and, the user does NOT have to include the % in the search string: Smile
Code:
LIKE  '%' || :Enter_part_of_the_description || '%'

In the aboe, the LITERAL % ( surrounded by single quotes ) indicates anything before text entered, and, anything after text entered. Additionally, two pipe characters || are used to indicate, concatenation.

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

In the first post here, jharris asked:
jharris wrote:
For Example, Between 6/15/2007 And 7/5/2007.
Another example: Between 8/2/2007 and 8/9/2007.

As of OpenOffice 3.1 ( and presumably afterwards, prior versions of OpenOffice does NOT support this syntax ) this may be accomplished with the following entry on the Criterion line, for your Table or View Date Column

Code:
BETWEEN :From_Date AND :To_Date

The above will provide the 'prompt' for the user to enter two dates, a "From_Date" and a To_Date" returning all records between AND including those dates.

Sliderule
Back to top
View user's profile Send private message
andrewtc
Newbie
Newbie


Joined: 10 May 2009
Posts: 2

PostPosted: Sun May 10, 2009 6:34 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun May 10, 2009 6:48 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
andrewtc
Newbie
Newbie


Joined: 10 May 2009
Posts: 2

PostPosted: Sun May 10, 2009 7:28 pm    Post subject: Solved Reply with quote

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. Laughing Laughing
Back to top
View user's profile Send private message
guachinver
Newbie
Newbie


Joined: 24 Dec 2009
Posts: 2

PostPosted: Thu Dec 24, 2009 5:40 am    Post subject: Concatenation not working on parameters querys? Reply with quote

I have just updated to 3.1 version Very Happy 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?Twisted Evil

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: Embarassed
SQL status: 3075
error:code -2147217900
The double pipe is not valid on query '`Name` LIKE '%' || :Enter_part_of_the_description || '%''.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Dec 24, 2009 7:20 am    Post subject: Reply with quote

guachinver:

You wrote that your SQL is:

Code:
SELECT *

   FROM `miag`

   WHERE `Name` LIKE '%' || :Enter_part_of_the_description || '%'


BUT ( if you are using HSQL - OpenOffice default database engine ), it should be:

Code:
SELECT *

   FROM "miag"

   WHERE "Name" LIKE '%' || :Enter_part_of_the_description || '%'


The difference, the TABLE and COLUMN ( field ) names are surrounded by double quotes ( " ) and string literals are surrounded by single quotes ( ' ).

Also, just so you know, the TABLE and COLUMN names must be spelled correctly as defined in your database, including CASE ( UPPER / Mixed / lower ).

VERY IMPORTANT: The above notation about || for concatenation assumes that your database engine ( you can confirm this by looking at the status line at the bottom ) supports it ( the SQL standard ). I wrote this 'assuming' the OpenOffice default database engine, HSQL is being used. If your database engine is NOT HSQL, but something else ( MySQL, Microsoft Access, SQLite, PostGRE, Firebird etc ) you will have to use that database engine's concatenation requirements. If you want, you can tell me your database engine, and, perhaps, I can advice you on that engine's concatenation syntax.

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

Sliderule
Back to top
View user's profile Send private message
guachinver
Newbie
Newbie


Joined: 24 Dec 2009
Posts: 2

PostPosted: Fri Dec 25, 2009 3:31 am    Post subject: Reply with quote

Quote:
If you want, you can tell me your database engine, and, perhaps, I can advice you on that engine's concatenation syntax.


The status line say: Microsoft Access 2007
Some idea for the concatenation and placeholders in LIKE?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group