Subject Re: SQL Query Front End
From Akshat Kapoor <akshat.kapoor@kapoorsons.in>
Date Thu, 14 Feb 2019 11:20:00 +0530
Newsgroups dbase.getting-started

On 14/02/2019 04:00, 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?
>


It will depend on what all you want the user to control in a query.
If you just want variation of dates or any other field then use of
params in queries is the easiest and best option.

the query will look like
q = new query()
q.sql = "select * from table where invoice_no =:invoice"
q.params["invoice"] = 0
q.active = true

Then in the form
q.params["invoice"] = form.entryfield.value
q.requery()

The above code is just to give you an idea. It may contain errors and
need adjustments.

Had this feature not been there it would have been damn difficult to
change query every time before generating a report everyday.

It is covered in Ken's book on reports and must be there in tutorials also.

If your this is your end requirement but are having difficulty in
executing it then send some more details of your requirement and will
try to send a demo form.

Regards
Akshat





Some code which I actually use
In datamodule
class dlsalesDATAMODULE of DATAMODULE
    this.SALES = new ADOQUERY(this)
    with (this.SALES)
       left = 2.0
       top = 2.0
       database = mcompany.adodb
       sql = 'Select sales.* , sales.qty* sales.rate - sales.misc as
amount from sales WHERE ddate = :dldate AND locat = :locat AND mast <>0
ORDER BY invoice_no asc , mast desc'
//Watch for word wrap in above sql statement
                params["dldate"].value=dtos(date())
                params["locat"].value="0"
                requestlive = false
       active = true
    endwith

And in the form
    function PRINT_REPORT_onClick()
          set procedure to dl_sales.rep
          r = new dl_salesreport()
          r.DLSALESDATAMODULE1.sales.params["dldate"].value = dtos(mdl_date)
          r.DLSALESDATAMODULE1.sales.params["locat"].value = mdl_locat
          r.DLSALESDATAMODULE1.sales.requery()
          r.DLSALESDATAMODULE1.tax.params["dldate"].value = dtos(mdl_date)
          r.DLSALESDATAMODULE1.tax.params["locat"].value = mdl_locat
          r.DLSALESDATAMODULE1.tax.requery()
          if r.DLSALESDATAMODULE1.sales.rowset.count() >0
             if r.printer.chooseprinter()
                r.title = "Daily Sales Report on "+dtoc(mdl_date)
                r.render()
             endif
          endif
          r = null
          release r

Again this is just to give you an idea of where and how params work. You
may not be using ADO or datamodules hence your syntax will be different
but will work in similar way.