distinguish between numeric 0 and null

[ITALIA]

I desperately need to find a way to distinguish between a numeric value of 0 (zero) and a numeric value of "null".
I found the following disarming note on the Alpha5 help page...

You cannot distinguish between numeric 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.

... and I also found some threads on this forum that talk about it, but I just thought "it's worth a try".

I'm just hoping there will be someone that will tell me that is not right and there's a way around it.

Basically, on a form I created a button to approove an Inward transaction; a transaction can be approoved only if the users entered all their charges (from another table) against it. The problem is that users are allowed to enter a value of 0 (zero) for their charges.
When they click on the APPROOVE button, if all the charges have been entered, <APPROOVE TRANSACTION>, otherwise <CAN'T APPROOVE>.

For example, if they entered 0 (or any other numeric value) for all charges, transaction can be approoved; if they didn't enter any value, transaction can't be approoved.

HOW CAN I CHECK IF THE VALUE ENTERED IS A ZERO OR IF NOTHING WAS ENTERED (NULL_VALUE)?

[MikeC]

Re: distinguish between numeric 0 and null

Mauro,
I did a short search and found this thread--there are more too but this one may do for you...
http://msgboard.alphasoftware.com/al...set+null+value

And here is the quote from Selwyn from a link given in the above thread:

you can use null_value() in an update operation against any data type now

LATER: Just did a short test on what was mentioned in the thread and the test seemed to work to determine if a field was 0 or blank ("null").
I used two numeric fields, one with a 0 value and the other with no entry (text set to ""). My button script was this for one of the fields.

if topparent:no2.text=""
    msgbox("This has a Null value")
 else
    msgbox("This has a 0 value")
end if

[ITALIA]

Re: distinguish between numeric 0 and null

Thanks Mike, I appreciate your quick reply.
Unfortunately I can't apply the solution that you suggested. See below.

I'm just going to explain some further details of the problem.
Basically, I deal with 2 forms: (1)INWARDS FORM (based on INWARDS.dbf table) and (2)CHARGES FORM (based on CHARGES.dbf table). INWARDS.dbf and CHARGES.dbf are linked in a set through the INWARDS NUMBER field.
From INWARDS FORM, users can access the CHARGES FORM (by clicking on the "ADD CHARGES" button) and enter their charges for that inward; on the CHARGES FORM, when they push the "SAVE CHARGES" button, the charges are correctly stored into CHARGES.dbf, the CHARGES FORM is closed automatically and the INWARDS FORM is reactivated.
Now, on the INWARDS FORM, there's another button: "APPROOVE INWARD". When users click on that button, I have to check if the charges were entered for the current Inward; unfortunately I can't check through the CHARGES FORM because the form was closed and that's why I can't apply the solution suggested by Mike above.
I can only check the CHARGES.dbf table, and apparently there's no way to distinguish between a numeric value of 0 (zero) and a numeric value of NULL.

The solution above applies to Strings (or Characters)

if topparent:no2.text=""
msgbox("This has a Null value")
else
msgbox("This has a 0 value")
end if

How can I get it to work with numbers?


NOTE: If my explanation is still crummy, let me know and I'll try to provide a graphic design of my forms.



MAURO

[MikeC]

Re: distinguish between numeric 0 and null

Mauro,

The No2 in my little sample script IS a numeric field but the test is done on the text value of that numeric field. As far a not being able to test on a form that is not open---if you are able to incorporate xbasic then the table the form is based upon can be opened and tested (then closed).

For me, and most others, the best, fastest, and easiest way to help someone is if you supply a stripped down version of what you want to do with the explanation of how and what you want accomplished. I've already used up my guess!! :)

[ITALIA]

Re: distinguish between numeric 0 and null

For me, and most others, the best, fastest, and easiest way to help someone is if you supply a stripped down version of what you want to do with the explanation of how and what you want accomplished

Regarding the above request, I'm scared my database is too big to upload.
Please give me the last chance to try be more comprehensive and then I'll think about how to upload a part of my database for you to test.


The solution below applies to fields on a form.

if topparent:no2.text=""
msgbox("This has a Null value")
else
msgbox("This has a 0 value")
end if

In my case, I have to deal with fields from a table (<tbl>.<field_name>)

I can't use the ".text" property on a table field. I can only use the "str()" method but unfortunately in Alpha5 str(null) == str(0) == "0".
(from a developer point of view this is pretty bad, str(null) should equal "" not "0").

I honestly believe there's no way out as this is how Alpha5 handles null numeric values.

FROM ALPHA5 V6 HELP PAGE
"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."

I'll probabily have to open the CHARGES form in background (somehow, maybe open it but not activate it or something like that) and then apply MIKE's solution using the ".text" property on the form fields. How painfull.

Mike, thanks again for your patience.



MAURO

[Tom Cone Jr]

Re: distinguish between numeric 0 and null

Mauro, maybe the solution is as easy as moving the validation script to the "Save Charges" button on the CHARGES form? Seems like you could check the "text" property of each numeric field and "cancel" both the "save" and the "form close" if any are still empty.

[MikeC]

Re: distinguish between numeric 0 and null

Mauro,
It seems that Alpha "almost" has things the way you need them but not quite---a Null value can be assigned to a numeric now as I indicated when I quoted Selwyn in a prior post but it is still treated as 0 at the table level or as a value.

So either utilize what Tom has indicated or another way if that is not feasible would be to have a logical field that is false by default and is set to True on the first entry in the field--so if untouched you can use the logical field as a test to whether the field is blank....but with existing records this would have to be manually done.

Or as you indicated use a hidden form that is never shown to test and then close it when done--this may be the easiest solution if Tom's idea doesn't work for you.

[DaveM]

Re: distinguish between numeric 0 and null

Mauro,

A shortcut might be to put in a field rule on those fields you check to insert a "0.00" (data entry/default) on a new record. Had the same problem and this is how I "fixed" it. A lot less code.