>>>Hello fellow viewers,
>>>
>>>I would like to include a numeric field in a view filter.
>>>
>>>However, when I do, and then bind that view to a query-by-example form, the numeric field cannot be null. This works fine as long as the user uses the numeric value to do the lookup, but what if he doesn't?
>>>
>>>If I set the .value of the field to "", I get a data type mismatch.
>>>
>>>Has anyone done this successfully? If so, how?
>>
>>If not NULLable, the empty numeric value is 0.
>
>Ed,
>
>I am making memory variables named after the ones I used in the filter.
>(e.g. for Custid = ?vp_custid, I create a mem var named vp_custid).
>
>I just set the memvar to "" if it's character and the view ignores it. Are you saying I should set the memvar to NULL if the field (in this case Custid) is numeric?
Only if the field is NULLable; there are simply too many times where 0 (the default EMPTY() for a variable type of N or Y) is a viable potential value - NULL is not 0, and is not empty, but NULL is not a viable numeric value; the data type taken on and return by VARTYPE() for a NULL valued numeric field is 'X'; this allows a distinction between a valid 0 value and 'no value'. The use of ISNULL() allows you to distinguish between the two; if you insist on using EMPTY() as a test, you lose the viability of 0 as a value.