Subject Re: Filter From Mattia <mattia@nospam.com> Date Wed, 15 Dec 2021 02:44:37 -0500 Newsgroups dbase.getting-started Hi Mervyn, very very good explanation for understanding what dBASE is doing behind the scenes. Compliments Mattia Mervyn Bick Wrote: > On 2021/12/14 06:00, AGOSTINHO wrote: > > Very strange behavior > > When trying from the command box  "SET FILTER TO BETJ_N <> 'J' then when browsing I get only records with the  betj_n " " (empty) non paid records > > but when applying this to the rowset property I don't get any records on the grid. > > What can be wrong here? > > There is nothing actually wrong.  It is a matter of understanding what > dBASE is doing behind the scenes. > > With the original DML commands dBASE did not differentiate between a > character field containing spaces i.e chr(32) and a character field > containing nulls i.e chr(0).  This decision was made long before > localSQL was included in dBASE. > > When you are working from the Command panel, SET FILTER TO uses the > original DML commands.  If you test for ' ' as the contents of a field > dBASE returns true for fields containing either chr(32) or chr(0) > > OODML (Object Oriented Data Manipulation Language) used for objects uses > localSQL to access tables. > > localSQL, which is a limited sub-set of SQL, does differentiate between > chr(32) and chr(0). > > In a query you can actually use > >    sql = 'select * from "debiteur_test.DBF" where betj_n is null or > betj_n = " "' > > > The filter property of a rowset object uses SQL expressions but it is > very limited. > >  From the help file > > Simple comparisons using the basic SQL comparison operators (=, <>, <, >  >, <=, >=) are supported; other predicates such as BETWEEN, IS NULL, IS > NOT NULL and LIKE are not. > > Chr(0) is not equal to chr(32) so setting the filter property to betj_n > = ' ' will not include records where betj_n contains NULL. > > The solution is to set the filter to  betj_n <> 'j' > > As both NULL and ' ' are not = 'j' records containing either ' ' or NULL > will be included. > > The alternative is either to save  ' ' to betj_n (or 'N' as Mattia > suggested) when a new record is added to the table. > > Mervyn.