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