Subject Re: Query error From Mervyn Bick <invalid@invalid.invalid> Date Sun, 3 Sep 2023 13:17:26 +0200 Newsgroups dbase.getting-started 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 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.