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

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

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.