Sounds simple, but a detailed search didn’t turn up much.
Form to enter Beginning and Ending Dates for a report, then run a report based on the date range
Button to run report can run macro and return SQL query by date range (form fields using ‘between’)
Marco can open report after query, but how to get report to use query results?
Report set to dynamic but no docs on running report conditionally
Hello,
There is a sample of this in the edited section of my answer here → Is there a way to have a form field's data in a base query for parameter (criteria) - #3 by Ratslinger
.
Note hat the query gets the dates by using a filter table.
https://ask.libreoffice.org/uploads/short-url/nW2PUGMTAXtJcBy67iiy8RvNj8m.odb
Open “Filter Form” to set filter.
Create a report from query “qFiltered2”. It will reflect the same filtered row set.
Simpler solution without filter form:
SELECT "D"."D" "Date", "P"."N" "Person Name", "C"."N" "Category", "D"."V" "Value", "D"."V" * 0.15 "VAT"
FROM "Categories" AS "C", "Data" AS "D", "Persons" AS "P"
WHERE "C"."ID" = "D"."CID" AND "P"."ID" = "D"."PID"
AND "D"."D" BETWEEN :Enter_Start_Date AND :Enter_End_Date
Store this query and create a report from it. You will be prompted for 2 dates every time you run the record.
So here is another oddity. In the query, the parameters are entered according to the spelling (accustomed to this):
.
But with the same query used in a report the sequence is different:
@Villeroy Thanks for that suggestion, pretty simple. However, when I enter the dates in Start and End as 11/01/22 and 11/30/22 it adds # front and back to each date entered automatically and converts year to 2022 and query returns null records even though there are 8. Running query with manually entered dates (#11/01/22# or #11/01/2022#) format works. Auto enter fails. Date field is MySQL field type date that displays as 2022-11-01.
Update: OK this is strange, if I run the query it returns zero records. If I run the report that is linked to the query I get the 8 records after entering the dates.
I don’t think so considering my last comment. Are you paying attention to what is being asked to be entered?
Ah, I see now. Image was a bit fuzzy but a closer look and I see start and end are reversed. Will use just for report so start before end. Thanks.