Subject Re: Query error From Tom <IHaveNoEmail@ddress> Date Sun, 3 Sep 2023 07:50:49 -0400 Newsgroups dbase.getting-started On 9/3/23 7:17 AM, Mervyn Bick wrote: > On 2023/09/02 18:06, Tom wrote: > >>> Try removing the WHERE CLAUSE and the "TestDate" parameter. >>> >>> I'm afraid I can't look at this further now and the power is going >>> off again in 30 minutes.  More tomorrow. >>> >>> Mervyn. >>> >> * >> Same error message ... >> * >> Take your time, I'm in no rush. >> >> Thanks for your efforts. >> >> Tom >> >> > > > Power was off again earlier this morning but we're back in business. > (For a while until the next outage at 18:00. :-( ) > > This old dog has just learnt a new trick. > > I haven't explored this too deeply but it looks as if a WHERE clause > doesn't recognise a calculated field.  The ORDER BY clause is, however, > quite happy to use the calculated field > > > sql = [Select CAST(d.TestDate as Char(10))Test_Date,d.Provider,] > sql += [d.Client,d.Category, d.Allergen, d.TestLevel, d.StdTestRange,] > sql += [n.Demodata_Notes ] > sql += [FROM DemoData d Left Join DemoNotes n ] > sql += [On n.Fk_DemoData = d.Pk_DemoData ] > sql += [ WHERE * added a space and a close bracket after the 'where' ... * > sql += [cast(d.TestDate as char(10)) LIKE :TestDate AND ] > sql += [LOWER(d.Category) LIKE :Category AND ] > sql += [LOWER(d.Allergen) LIKE:Allergen ] > sql += [ORDER BY Test_Date, Category, Allergen ] > params["TestDate"] = '%' > params["Category"] = '%' > params["Allergen"] = '%' > > Mervyn. > > > > * Hi Mervyn, With a slight change noted above, I copied and pasted your query to my form. I still get the same error message followed by dBase crashing. Not what I had hoped for. Tom