| Subject | Re: sql code |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Wed, 13 Jun 2018 21:48:56 +0200 |
| Newsgroups | dbase.getting-started |
On 2018-06-13 8:43 PM, Charlie wrote: > Hi... what is wrong here? I am trying to filter this sql statement with where using the variable talname, but I can't seem to get this to work for some reason. I've gotten so many different error messages my head is spinning. > > Really appreciate any help! > > sql = "select lname,sum(qty) as cnt, sum(qty*cost) as tcost, sum(qty*sell) as tsell from taldata where lname ="+talname+" group by lname" > In a different thread I've suggested a different approach. You don't really need to filter the totals rowset each time you want the values for a specific vendor. If you create the rowset with the totals for all vndors you only need to point to the correct record for the vendor you want. Still, if you want to do it this way then you should use a parameter driven query. form.q = new query() form.q.sql = "select lname,sum(qty) as cnt, sum(qty*cost) as tcost, sum(qty*sell) as tsell from taldata where lname = :tname group by lname" form.q.params['tname']= "" form.q.active = true. To get the values for a specific vendor form.q.params['tname'] = form.talname1.rowset.fields['talname'].value form.q.requery() or perhaps, if you have the vendor's name in a memory variable named, say, talname form.q.params['tname'] = talname form.q.requery() dBASE is not normally case sensitive but here the case of the parameter in the select statement must match the case of the params entry. The third alternative is to use the rowset's filter property. form.q.rowset.filter = [lname = ']+talname+['] Mervyn. | |