Check for Null in Numeric Field?

[jkletrovets]

Hello All

Even though the help file says the following:

Numeric
N
You cannot distinguish between records that have no value, and those that have a 0 in them. The .DBF table format, which Alpha Five uses, has no concept of a NULL numeric value.

<fieldname> = 0

I was wondering if anyone has ever created a work around to test whether a numeric field is Null? I have not been able to come up with anything in my testing.

Thanks,
Jeff

[MikeC]

Re: Check for Null in Numeric Field?

Jeff,
Check the numeric field's TEXT value to see if it ="" .


EDIT: Congrats on becoming yet another Alphaholic! Belated by 9 posts but I am only on about an hour a day of late (my excuse and sticking to it!).

[Tom Cone Jr]

Re: Check for Null in Numeric Field?

Jeff, the isblank() function can be used to determine if the text box bound to a numeric field is empty or not. You may not be working with a form or browse but I thought I'd mention it. -- tom

[csda1]

Re: Check for Null in Numeric Field?

Hi Jeff,

I was wondering if anyone has ever created a work around to test whether a numeric field is Null? I have not been able to come up with anything in my testing.

Mike's answer of testing the text value of the layout's object containing a field is correct. It will determine the difference.

There use to be support for the special value of MATH_NAN which stood for Not-A-Number, but I believe that it hasn't worked in ages (maybe a5v5). It would return a strange value (maybe very, very large or very, very small) that seemed to be reserved for this special case.

Another way for XBasic is to dimension as type A (any). e.g.

dim numval as a
numval=null_value()
?typeof(numval)
= "Z"
numval=""
?numval
= ""
?typeof(numval)
= "C"
numval=3
?typeof(numval)
= "N"
?numval
= 3

This allows storing a different types into a variable. If not numeric type, then consider it a numeric null. Anytime the user tries to write a null value in a dialog (to a character field), store 0 if there, or a "" if empty.

[jkletrovets]

Re: Check for Null in Numeric Field?

Thanks guys!

Mike's tip did it! My brain totally missed that option. :o

Tom....I have had erratic results with isblank() in the past so I try to avoid using it. I appreciate the option though.

Ira...thanks for the other example with the Type A variable. I think I can use that in something else I am getting ready to do.

Regards,

Jeff

[CALocklin]

Re: Check for Null in Numeric Field?

Here are a couple side FYIs to keep in mind when testing the form object's value (numeric fields only):

- If the object is defined with a format that includes a dollar sign, the result of val( <form>:<object>.text ) will be zero.

- If you get the value (as in <form>:<object>.value ), the result of a null field will be some huge number like 1.345*10^123. This is usually well outside the range of normal data and can therefore be used to determine if the value is null. Of course, if you are an astronomer or a chemist dealing with the number of molecules in some container, this may not work very well.

You should also keep that last issue above in mind if you believe that the field should never be blank - for example, if you set the default value to 0. In that case it should NORMALLY be OK but if someone imports a record with a blank value or deletes the value or something happens during development that allows a blank value to get in ....