[Solved] Way to pass parameters into queries
[Solved] Way to pass parameters into queries
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
-
- Volunteer
- Posts: 1544
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Way to pass parameters into queries
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
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.
Code: Select all
SELECT * FROM "MyTable" WHERE "StoredDate" >= :EnterMinimumDate
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Way to pass parameters into queries
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.
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
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Way to pass parameters into queries
Just like to add a quick note / explanation in addition to the wise comments by user Villeroy above.
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:
thereby, forcing the pop-up box to display the entries in more logical sequence.
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.
OpenOffice / LibreOffice Base will present the user the parameters the user must enter to complete the query in alphabetical order of the parameters.Villeroy wrote:
SELECT * FROM "TableX" WHERE "Date" BETWEEN :Enter_Start_Date AND :Enter_End_Date
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
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.