Subject Re: sql code From Charlie <tm@tc.com> Date Wed, 13 Jun 2018 16:45:20 -0400 Newsgroups dbase.getting-started Hi Mervyn.. Thanks again.  I actually could have done this much easier and faster using xmdl but wanted to learn how to do this so I was probably confused on the process.  But this should go well now... Mervyn Bick Wrote: > 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. >