[Solved] Way to pass parameters into queries

Creating tables and queries
Post Reply
DTC57
Posts: 4
Joined: Tue Oct 17, 2017 3:50 pm

[Solved] Way to pass parameters into queries

Post by DTC57 »

Is there a way to pass a parameter into a query from outside? For example, rather than using CURRENT_DATE I would like to provide the relevant date as a parameter that is then used by the query
Last edited by DTC57 on Thu Oct 19, 2017 8:55 am, edited 1 time in total.
OpenOffice 4.1.3 on Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Way to pass parameters into queries

Post by UnklDonald418 »

Parameters can be passed by prefixing a string with a colon. The string must not contain any spaces. When the query is run a parameter dialog will be displayed allowing the user to enter a value for the parameter. Something like

Code: Select all

SELECT * FROM "MyTable" WHERE "StoredDate" >= :EnterMinimumDate
There is information at
viewtopic.php?f=100&t=42845
In particular look at the example database LIKE_user_input_filter.odb found at the very end.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Way to pass parameters into queries

Post by Villeroy »

SELECT * FROM "TableX" WHERE "Date" BETWEEN :Enter_Start_Date AND :Enter_End_Date

This pops up a dialog with 2 input boxes labeled "Enter_Start_Date" and "Enter_End_Date". A named parameter has a leading colon and no spaces.
You can also substitute the parameter values with parent form values when you use that query in a subform.
download/file.php?id=26483

In many cases you can simply assign parent form fields (master fields) to subform fields (slave fields) so Parent.A = Slave.B AND Parent.X = Slave.Y
There are situations where you want to use other operators (<, >, >=, <=, BETWEEN) or where you want to use functions as in Round(Parent.X, 2) <= Round(Slave.Y, 2). This is where parameter queries give you more flexibility.
Image
Subform properties linking a slave parameter pD to a master field "D". In the query definition the named parameter is written with a leading colon as :pD
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Way to pass parameters into queries

Post by Sliderule »

Just like to add a quick note / explanation in addition to the wise comments by user Villeroy above.
Villeroy wrote:
SELECT * FROM "TableX" WHERE "Date" BETWEEN :Enter_Start_Date AND :Enter_End_Date
OpenOffice / LibreOffice Base will present the user the parameters the user must enter to complete the query in alphabetical order of the parameters.

Therefore, in the example above, since :Enter_End_Date is before :Enter_Start_Date ( in alphabetical order ) , I would prefer to define the Query ( with parameters ) as:

Code: Select all

SELECT 
   * FROM "TableX" 
WHERE "Date" BETWEEN :Enter_01_Start_Date AND :Enter_02_End_Date
thereby, forcing the pop-up box to display the entries in more logical sequence. :super:

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

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Post Reply