Subject Re: Query error From Tom <IHaveNoEmail@ddress> Date Sat, 2 Sep 2023 08:04:03 -0400 Newsgroups dbase.getting-started On 9/2/23 7:32 AM, Mervyn Bick wrote: > On 2023/09/02 13:29, Mervyn Bick wrote: >> On 2023/09/02 13:00, Tom wrote: >> >>> Revision: >>> >>> sql = "Select CAST(TestDate as Char(10)) cTestDate, d.TestDate, >>> d.Provider, d.Client, d.Category, d.Allergen, d.TestLevel, >>> d.StdTestRange, n.Demodata_Notes FROM DemoData d Left Join DemoNotes >>> n On n.Fk_DemoData = d.Pk_DemoData WHERE cTestDate LIKE :TestDate AND >>> LOWER(d.Category) LIKE :Category AND LOWER(d.Allergen) LIKE :Allergen >>> ORDER BY TestDate, Category, Allergen" >>> >>> gets me an 'Invalid Field Name' error message. :( >> >> Our messages have crossed. >> >>    CAST(d.TestDate as char(10)) cTestDate,..... >> >> >> The field in the CAST() function must be qualified as to whai tabe it >> comes from. >> >> Mervyn. >> > > Ops, I missed something else which will also cause a problem.  As you've > used a new name for the calculated field you need to use this new name > in the ORDER BY clause. > >    Order by cTestDate,category, allergen > > Mervyn. > > Sorry Mervyn but I am still confused here. The following 'cut down' version still has the 'invalid field name' error. Trying to get this to work correctly then I can add back the rest of my original query. Revision2: * sql = "Select CAST(d.TestDate as Char(10)) Test_Date, d.TestDate, d.Provider, d.Client, d.Category, d.Allergen, d.TestLevel, d.StdTestRange, n.Demodata_Notes FROM DemoData d Left Join DemoNotes n On n.Fk_DemoData = d.Pk_DemoData WHERE Test_Date LIKE :TestDate" * params["TestDate"] = "%" Tom