Subject Re: sql where statement not working correctly From Mervyn Bick <invalid@invalid.invalid> Date Sat, 14 Jul 2018 12:52:18 +0200 Newsgroups dbase.getting-started On 2018-07-14 12:03 PM, Charlie wrote: > this.SPDDATAARCH2 = new QUERY(this) >     with (this.SPDDATAARCH2) >        left = 40.0 >        width = 10.0 >        height = 1.0 >        database = form.train1 >        sql = "select sum(qty) as qqty from SPDDATAARCH.DBF where namedate=trim('spdname')" >        active = true >     endwith > > for some reason the sql statement doesn't seem to work for me.  It works without the where clause, but does not with it in. > > namedate is a field in the table, spdname is a variable. > > without the where clause I get a whole number that is not filtered.  If I try to filter it with the where clause I get a decimal point.  The number should actually be 16. > > Can anyone help? > > Thanks much! > You need to pass the value in as a parameter this.SPDDATAARCH2 = new QUERY(this)     with (this.SPDDATAARCH2)        left = 40.0        width = 10.0        height = 1.0        database = form.train1        sql = "select sum(qty) as qqty from SPDDATAARCH.DBF where namedate=:spname"        params['spdname'] = trim('spdname')        active = true     endwith To find a qqty for a different namedate     form.spddataarch2.params['spdname'] = trim(form.entryfield1.value)     form.spddataarch2.requery() If you are going to need to find values for more than one value of spdname an alternative is to create a rowset of sums for all spdnames. this.SPDDATAARCH2 = new QUERY(this)     with (this.SPDDATAARCH2)        left = 40.0        width = 10.0        height = 1.0        database = form.train1        sql = "select namedate,sum(qty) as qqty from SPDDATAARCH.DBF group by namedate" This will give you a rowset with a record containing namedate and qqty for each value of namedate. You can then fetch the value for any namedate by using oReef.applyLocate() Mervyn        active = true     endwith