Hi Hilmar,

Ordinarily I would agree with you, but FoxPro is doing some strange things with null values within the SELECT - SQL command. In several reports, I specified a column header name for the field that contains null values. The expression that the new column header is based on uses macro substitution. For example: "&lab1. AS sodium"

The macro substituted variable is built from the option that the user makes from a drop down list. This was the most efficient way to do this, since there are about 60 possible selections. The problem is the FoxPro is not correctly representing the null values in the cursor that I dump the data into. Instead, the select - sql command retrieves the old value, and not the current null status, which makes no sense at all. The field is set to null, but the query displays the value entered previous to it being changed to null (sometimes the user changes a test value to null). Anyway, I don't understand why FoxPro is pulling up old values that should have been erased when the field was changed to null. Maybe I don't really understand exactly how NULL works in a FoxPro field. I understand what role Null is supposed to fill, but it acts very strange. I'm not sure if the macro substitution is causing some kind of conflict or what. This is messing up all of the report statistics, which has made it necessary for me to try using BLANK instead. I don't like representing missing data this way, but the NULL values have not worked right. Has anyone else ever noticed that SELECT - SQL pulls up old values from fields that are supposed to be null?

To make matters worse, the ISBLANK() function is also acting strange within my SELECT - SQL commands. Well, I'm going to experiment with the NVL function that you mentioned, which may provide me with a new approach.

Dave


>>Hi Mike,
>>
>>Yes, actually in this case it matters. My client's software collects lab test values, and they need to distinquish between zero and nothing entered. Some of their lab values can range from an inputted zero to something else. Blank would just indicate that they received no data for that particular test. I'm in fact using ISBLANK() to make that distinction. Some of their test ranges consider zero as being an abnormal value, so I need to make the distinction.
>>
>>I played around with using NULL, but it had some weird side effects in the results of my report programming. I could have gotten around that, but it's easier just to use BLANK AND ISBLANK(). Also, I'm sure that NULL would confuse my client, more than it would be helpful. I was wondering if there was a keyboard shortcut for turning a field blank, instead of needing to use code with the BLANK command. Kind of like the "Ctrl 0" for Null. But, I guess that doesn't exist, so I'm going to put some code into the rightclick event to blank out the field.
>>
>>The annoying thing is that the field is initially blank, but you cannot manually blank it out after you type into the field. Zero doesn't appear initially, but will always appear after you've inputted into that particular field for that record (unless you program a blank routine for it). Oh well.
>>
>>Dave
>>
>>>>I need to provide an easy way for users to blank out a numerical field that contains previous input. VFP won't allow one to just delete the field's value, which I don't understand why. When I attempt to delete the value from the field, the field automatially resets itself to zero. I don't want to use null values for practical reasons.
>>>
>>>Well, even when the field is blank it is equal to 0. Does it really matter whats shown? Anyways, you can get rid of the 0 by doing a BLANK on the field. I think the only time its truly relevent, however, is if you have logic that uses IsBlank() on the field.
>
>You can use BLANK, of course. But please consider:
>
>

.NULL. values are the most logical way to represent missing information, and it is precisely toward this purpose that .NULL. values were introduced in VFP.
>

There is a built-in shortcut key to NULL a value.
>

You can display NULL values to the end-user in any way you like. For instance, SET NULLDISPLAY TO "???" will show three question marks for missing data.
>

With nvl(), you can convert null values to other values. For instance, change missing data to 0 for a certain calculation.
>
>What other problems did you have with NULL values?
>
>Hilmar.