[Solved] Query based on form fields

[TondaBucek]

Hi, I want to do in my opinion easy task, but it drives me crazy.
I have a simple table with DATE column. The only think I want to do is make form, in which user select to dates in date input field and the form displays all records from the table between this two dates.

I created this sql which works fine: SELECT * FROM "MyTable" WHERE "Date" >= {D '2020-05-15' } AND "Date" <= {D '2020-05-16' }

But instead of fixed dates I want it replace by user input. So I thought that user will select the dates in form and I attach somehow this sql to push button. But I don't know how.
I don't insist on using the push button', but it must be somehow controlled from the form, not from oo Base filtres etc.
Anybody any idea, pls?
Thanks

[Villeroy]

menu:Tools>SQL....

Code: Select all

CREATE TABLE "Filter"(ID INT PRIMARY KEY, DT1 AS DATE, DT2 AS DATE);
INSERT INTO "Filter" VALUES(0, NULL, NULL);

menu:View>Refresh Tables.
-------------------------------------------------
Bind a form to SQL statement:

Code: Select all

SELECT * FROM "Filter" WHERE ID=0

Important: On the form's data tab disable everything except "Allow Modifications" (no toolbar, no tabbing into records other than the one with ID=0)
Add 2 date controls for the from-date DT1 and the until-date DT2 with optional input ("Input Required" = No)
-------------------------------------------------
Bind a subform to this statement:

Code: Select all

SELECT "MyTable".* 
FROM "MyTable" 
WHERE ("Date">= :pDT1 OR :pDT1 IS NULL) AND ("Date"<= :pDT2 OR :pDT2 IS NULL)

Add a push button with Action = Refresh Form" to the subform and design the subform as you like.
------------------------------------------------
Now you can enter one, two or no dates into the filter form and refresh the subform which is filtered by the parameter query.

You may create another subform based on an aggregation query like this:

Code: Select all

SELECT AVG("Amount") AS "Average", MIN("Amount") AS "Minimum", MAX("Amount") AS "Maximum"
FROM "MyTable", "Filter" 
WHERE "Filter"."ID"=0 AND ("MyTable"."Date">= "Filter"."DT1" OR "Filter"."DT1" IS NULL) 
  AND ("MyTable"."Date"<= "Filter"."DT2" OR "Filter"."DT2" IS NULL)

You may create a report based on this query and the report will use the same filter that has been set in the filter form

Code: Select all

SELECT "MyTable".* 
FROM "MyTable", "Filter" 
WHERE "Filter"."ID"=0 AND ("MyTable"."Date">= "Filter"."DT1" OR "Filter"."DT1" IS NULL) 
  AND ("MyTable"."Date"<= "Filter"."DT2" OR "Filter"."DT2" IS NULL)

---------------------------------------------
For another criteria set, just add a row to "Filter" and use another row ID.
For more filter criteria add more columns to "Filter" with optional input.

[TondaBucek]

Thank you very much. Some stepts were not clear for me because I am new to OO Base. But I found LibreOffice serial on YouTube and realized thet LibreOffice is almost the same as OpenOffice. So with help of that tutorial and your description and SQLs I managed my task. Thanks
The LibreOffice tutorial which helped me a lot: https://www.youtube.com/playlist?list=P ... 4CYHMr4wmV

[Villeroy]

The SQL statements (CREATE, INSERT, SELECT) are easier to communicate and to execute than step-by-step instructions clicking through the graphical user interface. SQL is not Base specific. The same (or similar) statements work with any other relational database.

[TondaBucek]

Yes, the SQL is clear to me But I didn't know e.g. how to add a button to sub-form since it is GUI related. But that was explained in the tutorial. So with your help I was able to realized what I really need to learn and found out and finished what i needed. Thanks