Numeric field: how to distinguish between no value and 0 value 
Author Message
 Numeric field: how to distinguish between no value and 0 value

Is there a function which can distinguish between a numeric field for
which no value has been entered and one for which a 0 value has been
entered? When I browse the table I see that the field is blank if no
value has been entered and shows '0' if 0 has been entered, so there
must be some information stored in the table to differentiate between
these two cases, but I can't find any way to access that information
programmatically. I've tried ISNULL( ) and EMPTY( ) but these give the
same results in both cases. Any ideas?

Thanks,
Bert



Sun, 21 Oct 2001 03:00:00 GMT  
 Numeric field: how to distinguish between no value and 0 value
Hi Bert

There might be a better way, but this method seems to work.

Regards

Jon

FUNCTION FLDBLANK(cName)
LOCAL nRecSize
LOCAL nHeader
LOCAL cBuffer
LOCAL nOldRec
LOCAL cDbf
LOCAL cAlias
LOCAL lBlank
LOCAL nHandle
LOCAL cTagName
LOCAL aFldList
LOCAL nFldCount
LOCAL nOffset
LOCAL nFldSize
LOCAL nField
LOCAL lFound
LOCAL cExclusive
lBlank = .F.
cAlias = ALIAS()
IF !EMPTY(cAlias)
        cExclusive = SYS(2011)
        cDbf = DBF()
        cTagName = TAG()
        nOldRec = RECNO()
        nRecSize = RECSIZE()
        nHeader = HEADER()
        DIMENSION AFLDLIST[1,4]
        nFldCount = AFIELDS(aFldList)
        cName = UPPER(cName)
        nOffset = 1
        nFldSize = 0
        nField = 1
        lFound = .F.
        DO WHILE nField <= nFldCount .AND. !lFound
                nFldSize = aFldList[nField, 3]
                IF aFldList[nField, 1] == cName
                        lFound = .T.
                ELSE
                        nOffset = nOffset + nFldSize
                ENDIF
                nField = nField + 1
        ENDDO
        IF lFound
                USE IN (cAlias)
                nHandle = FOPEN(cDbf)
                FSEEK(nHandle, nHeader + 1 + (nOldRec - 1) * nRecSize, 0)
                nRecSize = nRecSize - 1 && for deleted flag
                cBuffer = FREAD(nHandle, nRecSize)
                IF FERROR() == 0 .AND. LEN(cBuffer) == nRecSize
                        lBlank = SUBSTR(cBuffer, nOffset, nFldSize) == SPACE(nFldSize)
                ENDIF
                FCLOSE(nHandle)
                IF cExclusive == "Exclusive"
                        USE (cDbf) ALIAS (cAlias) EXCLUSIVE
                ELSE
                        USE (cDbf) ALIAS (cAlias) SHARED
                ENDIF
                IF !EMPTY(cTagName)
                        SET ORDER TO (cTagName)
                ENDIF
                GOTO (nOldRec)
        ENDIF
ENDIF
RETURN lBlank



Quote:
> Is there a function which can distinguish between a numeric field for
> which no value has been entered and one for which a 0 value has been
> entered? When I browse the table I see that the field is blank if no
> value has been entered and shows '0' if 0 has been entered, so there
> must be some information stored in the table to differentiate between
> these two cases, but I can't find any way to access that information
> programmatically. I've tried ISNULL( ) and EMPTY( ) but these give the
> same results in both cases. Any ideas?

> Thanks,
> Bert



Sun, 21 Oct 2001 03:00:00 GMT  
 Numeric field: how to distinguish between no value and 0 value
Hi Bert

Last message was a bit of overkill - try ISBLANK().

Regards Jon



Quote:
> Is there a function which can distinguish between a numeric field for
> which no value has been entered and one for which a 0 value has been
> entered?
> <SNIP>



Sun, 21 Oct 2001 03:00:00 GMT  
 Numeric field: how to distinguish between no value and 0 value
ISBLANK()
Don't build any kind of validation on this. The blank can change to a 0 as a
side effect of any number of other operations.  It's totally  inconsistent,
unstable and unreliable. Make the field nullable and use NULL to show
unknown status.
-Anders


Quote:
> Is there a function which can distinguish between a numeric field for
> which no value has been entered and one for which a 0 value has been
> entered? When I browse the table I see that the field is blank if no
> value has been entered and shows '0' if 0 has been entered, so there
> must be some information stored in the table to differentiate between
> these two cases, but I can't find any way to access that information
> programmatically. I've tried ISNULL( ) and EMPTY( ) but these give the
> same results in both cases. Any ideas?

> Thanks,
> Bert



Sun, 21 Oct 2001 03:00:00 GMT  
 Numeric field: how to distinguish between no value and 0 value
hi bert,

as far as i remember it, a 0-value is the same as empty for numerical
fields, .NULL.  however are different.
your empty display may comes from imported data, e.g. dBase format.

in order to achieve the same for your users, you would thus give them
the possibility to insert .NULL. and then SET NULLDISPLAY TO "" (for
this field or globally).

hope this helps,
                  heinz-dieter

Quote:

> Is there a function which can distinguish between a numeric field for
> which no value has been entered and one for which a 0 value has been
> entered? When I browse the table I see that the field is blank if no
> value has been entered and shows '0' if 0 has been entered, so there
> must be some information stored in the table to differentiate between
> these two cases, but I can't find any way to access that information
> programmatically. I've tried ISNULL( ) and EMPTY( ) but these give the
> same results in both cases. Any ideas?

> Thanks,
> Bert



Sun, 21 Oct 2001 03:00:00 GMT  
 Numeric field: how to distinguish between no value and 0 value

Quote:

>hi bert,

>as far as i remember it, a 0-value is the same as empty for numerical
>fields, .NULL.  however are different.
>your empty display may comes from imported data, e.g. dBase format.

Actually, the empty display came from using INSERT INTO ... FROM
MEMVAR, with the memvars not defined for some of the fields.

Quote:

>in order to achieve the same for your users, you would thus give them
>the possibility to insert .NULL. and then SET NULLDISPLAY TO "" (for
>this field or globally).

>hope this helps,
>                  heinz-dieter

I just "discovered" the NULL property for fields, but I wasn't aware
of the SET NULLDISPLAY command (I'm transitioning from 2.x to VFP so
there are a lot of commands I'm not aware of yet :) ). I'll probably
use this approach in the future, but for now the ISBLANK() function
suggested by Anders and Jon seems to work OK. Thanks to all.

Bert



Sun, 21 Oct 2001 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Store a Value to specificly numeric and a question about Value of txtBox

2. VAL for Numeric Value, STR for Character Value but what about DATETIME to a Numeric Value?

3. Distinguishing between zeroes and blanks in numeric field

4. Numeric field values corrupted by Access / Excel?

5. VFP5.0: Character fields with Null values will not accept text values

6. procedure return value not numeric if procedure not include return command

7. comparing a textbox value as numeric

8. Restoring Numeric Values To Default

9. VFP BIG BUG on numeric values

10. Combo/Listbox: Numeric values?

11. Copy to TYPE DELIMITED rounds numeric values

12. VFP6 SELECT SQL and numeric values

 

 
Powered by phpBB® Forum Software