Subject Re: SQL Query Front End
From Dan Bernard <dbernard@dicarlofood.com>
Date Thu, 14 Feb 2019 10:07:19 -0500
Newsgroups dbase.getting-started

Akshat Kapoor Wrote:

> 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.
Ashkat,

Thank you for your response and code sample, I will give it a try.

Dan