Subject Re: SQL Query Front End From Mervyn Bick <invalid@invalid.invald> Date Fri, 15 Feb 2019 10:07:30 +0200 Newsgroups dbase.getting-started Attachment(s) orders_report.zip On 2019-02-14 12:30 AM, Dan Bernard wrote: > Is it possible to build a front end to a SQL query that would allow my users to enter variables, ie. a date range, and run a report?  If so, are there examples? > I've had second thoughts on this.  You mentioned SQL query and my mind immediately jumped to a query accessing a SQL server such as Firebird or MySQL and my initial reply was based on this.  But all queries in OODML are actually SQL queries.  If you meant a query accessing .dbf tables then there is no problem with an example. And even though the example uses a .dbf table the same principles apply where the tables are on a SQL server although the syntax for accessing the tables may differ slightly. To select records from a table which meet specific conditions one would use a WHERE clause in the SQL property of a query.  The selection criteria can be hard-coded in the WHERE clause but to enable the user to change the criteria at will, e.g to select a new date range, a parameter driven query is probably the best solution.  Change the parameters, execute the query's requery() method and you have the required new rowset. There are various ways of passing parameters to a report so that selected records can be included.  The method that seems to be used most is to use a form to create an instance of the report in memory and then assign the instance of the report to a user-defined property of the form. With the instance of the report in memory and accessible to the form you can change ANYTHING in the report before it is rendered.  You can change the record selection, change (or add or delete) text on the report, specify a printer, specify which printer paper tray to use and so on. Once all the changes have been passed to the report the report can be rendered with all the changes in place. In the attached example the initial dates for the parameters and the entryfields may look a bit strange. params["sdate"] = dttod(new date(1900,0,1)) params["edate"] = dttod(new date(2020,0,1)) These initial (and completely arbitrary) values were chosen so that all records in the table will display when the form opens. I have used this rather convoluted method as I don't know what the date format is set to on your computer.  If I guessed wrong you would have had to deal with an error message. :-( The first time you open the form or the report in a designer the dates will be replaced with literal dates such as {1900-01-01} or {01-01-1900} depending on the date format you use.  Because the command "new date()" returns a date and time I've used dttod() to get rid of the time. Mervyn.