Re: Coerce Null to Zero?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Dmitriy Antonov wrote:
"Karl E. Peterson" <karl@xxxxxxxx> wrote in message
news:OL6ZCjL7GHA.4552@xxxxxxxxxxxxxxxxxxxxxxx
Just wondering... When working with database fields, it's simple to
coerce
a Null to String by concatenating an empty string. IOW:

MyField & ""

Is there a similar technique for coercing it to Zero with numeric
fields? I
swear I remember knowing of one at one time, but ...? All I can
think of is:

Val(MyField & "")

Sure seems klunky.

(Null And 0)


Try
?5 + (Null And 0)
5

Yeah, that's the idea! Only problem is, if the field isn't Null, it fails.
Say, the field holds 12:

?5+(12 And 0)
5

Ideally, I'd want that to return 17. IOW, just consider the field at its
"face value" if not Null, and consider it 0 if it is Null.

But I generally prefer to use generic function. Something like this
(got idea from MS Access):

public function nz(Value as Variant, optional ValueIfNull as
Variant=vbNullString) as Variant
if isnull(Value) then nz=ValueIfNull Else nz=Value
end function

So any of the following should work with numeric (integer) value:
lRes = nz(Null, 0)
lRes = Val(nz(Null))

I prefer an empty string to zero as a default value, because it is
easier to convert to zero from an empty string then vice versa.

Should be adjusted if objects can be considered, but, generally, no
need for this.

That's another thought, yeah. Might have to go that way, if the idea of
Val() becomes sufficiently disquieting. Thanks!
--
Working without a .NET?
http://classicvb.org/


.

Relevant Pages

  • Re: Coerce Null to Zero?
    ... Is there a similar technique for coercing it to Zero with numeric fields? ... lRes = Val) ... I prefer an empty string to zero as a default value, ...
    (microsoft.public.vb.syntax)
  • RE: How does defined work?
    ... Both Zero "0" and empty string " " are defined values in Perl, ... ||I am posting this query in beginners instead of beginners-cgi because I ... ||How can I make this execute the die statement when userId is missing from ...
    (perl.beginners)
  • Re: Validation Rule
    ... I have checked the fields required porperty to yes and allow zero length ... anyway and I select no and get an action failed dialogue message. ... prevents empty string values from being saved. ...
    (microsoft.public.access.formscoding)
  • Re: strncmp(a,b,0)
    ... > Now I have it to return zero. ... characters (characters that follow a null character are not ... or less than the possibly null-terminated array ... Don't you think one empty string is equal to another empty string? ...
    (comp.lang.c)
  • Re: Update table with Null data
    ... NOTE that inserting a null into a field or leaving it null is never a good ... an empty string or even the word "Null" allows Access to actually use ... > actually a zero length string. ... > Microsoft Access MVP ...
    (microsoft.public.access.forms)