Subject Re: DBF level 4 to 7 Problem
From Peter <phb2020@hotmail.com>
Date Tue, 14 Mar 2017 11:38:23 -0400
Newsgroups dbase.getting-started

Mervyn Bick Wrote:

> On 2017-03-14 9:31 AM, WJS wrote:
> > On 2017/03/13 11:19 PM, Peter wrote:
> >> ......
> >> Thank your both for your quick responses.
> >> About a year ago I changed another dbf from level 4 to 7, and got
> >> messed up math with a numeric field becoming null instead of zero.
> >> Mervyn saved the day with a short and highly effective program to
> >> replace a numeric field with a null value to 0.00.  Will give it a
> >> try.  Certainly sounds like that is the problem.
> >> Will report back.
> >> Peter
> >>
> >
> > Don't know Mervyn's solution but just FWIW:
> > You can also use local sql directly e.g.
> >
> > update <tableName> set <fieldName1> = <value>, <fieldName2> = <value>
> > where (<fieldName> IS NULL OR <fieldName2> IS NULL)
> >
>
> I can't remember what I had for lunch yesterday so it's no wonder I
> can't recall what I posted a year ago. :-)
>
> Be careful with this.  As it stands that line of code could have
> unexpected results.
>
> If both fields are null there isn't a problem but if fieldname1
> contains, say 15.00 and fieldname2 is null the code will set BOTH fields
> to 0.00 (or whatever value has been assigned to the specific field).
>
> It would be better to do one field at a time.
>
>   update <tableName> set <fieldName1> = <value> where (<fieldName1> IS NULL)
>
> update <tableName> set <fieldName2> = <value> where (<fieldName2> IS NULL)

As you have understood right from the start (and is noted in language reference when changing level), the change from Level 4 to 7 has changed any field that is empty to null.
Mervyn Bick's program is:


q = new query()
q.sql = "select * from 'c:\pat\data\patient.dbf'"
q.active = true
q.rowset.first()
do while not q.rowset.endofset
     for n = 1 to q.rowset.fields.size
        if q.rowset.fields[n].type = "C"   // or "N"
           if q.rowset.fields[n].value = null
              q.rowset.fields[n].value = ""   // 0 for "N"
          endif
          ? q.rowset.fields[n].value
        endif
     next
     q.rowset.next()
enddo
q.active = false

There are no empty number fields to mess with. Hopefully Logical fields are not affected.  But I do have some date fields that are empty, and are changed to null.  What string do I use to change these null date fields to empty strings?  Same as for Character fields, ie ""?  {} does not work, nor {  /  /    }, nor ctod(  /  /    ).
Peter