Subject Re: filter on logical plus date field From Rouke <moc.liamg@rekkabrg> Date Fri, 07 Jan 2022 04:19:50 -0500 Newsgroups dbase.getting-started Thanks Mervyn. Mervyn Bick Wrote: > On 2022/01/06 21:27, Rouke wrote: > > Thanks, > > > > set filter to not (returned=true or not isblank(dateofrtrn)) > > > > seems to give the required result. > > > > Any idea how to do this in local sql? > > > > Where NOT (loans.RETURNED = True OR NOT isblank(loans.DATEOFRTRN)) > > > > doesn't get accepted. > > > > As Ken has said elsewhere in the thread, logical decisions can be > tricky.  :-(  Adding brackets into the mix can change the whole thing. > Sometimes you need them, most times you don't.  The tests inside the > brackets get evaluated first. > > set filter to not (returned=true or not isblank(dateofrtrn)) > > The test inside the brackets will find all records with > >      returned = true whether dateofrtrn has a value or not >      returned = false where dateofrtrn has a value. > > The NOT outside the brackets negates the test inside the brackets so it > finds all records with > >     returned = false where dateofrtrn does not have a value > > set filter to returned = false and isblank(dateofrtrn) > > will give you exactly the same result and requires less mental > gymnastics to work out what's happening behind the scenes.  :-) > > Isblank() is a XDML function, not a localSQL function so it won't work > in a WHERE clause in a SELECT statement. > > > Try the following > >    .... Where loans.RETURNED = True and loans.DATEOFRTRN is null > > Mervyn > > > > >