Coerce Null to Zero?

Karl E. Peterson · Joined: 04 Oct 2007 Posts: 4836

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.

Thanks!
--
Working without a .NET?
http://classicvb.org/

Archived from group: microsoft>public>vb>syntax

"Rick Rothstein \ · Joined: 04 Oct 2007 Posts: 1584

> 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.

No more so than this...

Format(MyField, "0;;;0")

Rick

Bob Butler · Joined: 04 Oct 2007 Posts: 1325

"Karl E. Peterson" wrote in message@TK2MSFTNGP05.phx.gbl
> 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 & "")

I think that's it unless you want to use IIF; at least the Val works for
numerics -- for date types I don't think I've ever found a way other than an
IsNull test of some sort

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

Karl E. Peterson · Joined: 04 Oct 2007 Posts: 4836

Bob Butler wrote:
> "Karl E. Peterson" wrote ...
>> 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 & "")
>
> I think that's it unless you want to use IIF; at least the Val works
> for numerics --

Okay, just wanted to be sure I wasn't forgetting some arcane slickness.

> for date types I don't think I've ever found a way
> other than an IsNull test of some sort

Hmmmm, store them as Doubles, then do a variation on above?

Thanks... Karl
--
Working without a .NET?
http://classicvb.org/

Karl E. Peterson · Joined: 04 Oct 2007 Posts: 4836

Rick Rothstein (MVP - VB) wrote:
>> 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.
>
> No more so than this...
>
> Format(MyField, "0;;;0")

Heh, true that! Especially since you can't even do *math* with that one,
yet.
--
Working without a .NET?
http://classicvb.org/

Bob Butler · Joined: 04 Oct 2007 Posts: 1325

"Karl E. Peterson" wrote in message@TK2MSFTNGP02.phx.gbl
> Bob Butler wrote:
>> "Karl E. Peterson" wrote ...
>>> 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 & "")
>>
>> I think that's it unless you want to use IIF; at least the Val works
>> for numerics --
>
> Okay, just wanted to be sure I wasn't forgetting some arcane
> slickness.
>
>> for date types I don't think I've ever found a way
>> other than an IsNull test of some sort
>
> Hmmmm, store them as Doubles, then do a variation on above?

I'd do VB.Net first!

you could always coerce the value in the query...
case when myfield is null then 0 else myfield end as myfieldnz

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

Bob O`Bob · Joined: 04 Oct 2007 Posts: 1456

Karl E. Peterson wrote:
> 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.
>
> Thanks!

I just add zero.

Bob

Karl E. Peterson · Joined: 04 Oct 2007 Posts: 4836

Bob O`Bob wrote:
> Karl E. Peterson wrote:
>> 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.
>>
>> Thanks!
>
> I just add zero.

?null+0
Null

Confused...?
--
Working without a .NET?
http://classicvb.org/

Dmitriy Antonov · Joined: 04 Oct 2007 Posts: 431

"Karl E. Peterson" wrote in message @TK2MSFTNGP05.phx.gbl...
> 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.
>
> Thanks!
> --
> Working without a .NET?
> http://classicvb.org/
>
>

(Null And 0)

Try
?5 + (Null And 0)
5

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.

Dmitriy.

Karl E. Peterson · Joined: 04 Oct 2007 Posts: 4836

Dmitriy Antonov wrote:
> "Karl E. Peterson" wrote in message
> @TK2MSFTNGP05.phx.gbl...
>> 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/

Dmitriy Antonov · Joined: 04 Oct 2007 Posts: 431

"Dmitriy Antonov" wrote in message %23iJGN7GHA.2384@TK2MSFTNGP04.phx.gbl...
>
> "Karl E. Peterson" wrote in message
> @TK2MSFTNGP05.phx.gbl...
>> 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.
>>
>> Thanks!
>> --
>> Working without a .NET?
>> http://classicvb.org/
>>
>>
>
> (Null And 0)
>
>
> Try
> ?5 + (Null And 0)
> 5
>
>
> 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.
>
> Dmitriy.
>
>

I probably had a bad day today.
Please disregard the first part - of course ANDing a variable with zero is
not going to work (and doing it with a constant does work but doesn't make
any sense). So I would stick to just a function.

Dmitriy.

Bob O`Bob · Joined: 04 Oct 2007 Posts: 1456

Karl E. Peterson wrote:
> Bob O`Bob wrote:
>> Karl E. Peterson wrote:
>>> 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.
>>>
>>> Thanks!
>> I just add zero.
>
> ?null+0
> Null
>
> Confused...?

I don't think I am.
I never tried to /use/ such values directly, butI always assign them to a numeric variable

numericvar = field.value + 0

It's been quite a while, but as I recall, it always worked.

Bob
--

"Rick Rothstein \ · Joined: 04 Oct 2007 Posts: 1584

>>> I just add zero.
>>
>> ?null+0
>> Null
>>
>> Confused...?
>
>
> I don't think I am.
> I never tried to /use/ such values directly, butI always assign them to a
> numeric variable
>
> numericvar = field.value + 0
>
> It's been quite a while, but as I recall, it always worked.

The following

Dim IntVal As Integer
IntVal = Null + 0

errors out on the 2nd statement with Run-time error '94', "Invalid use of
Null"

Rick

"Rick Rothstein \ · Joined: 04 Oct 2007 Posts: 1584

>>>> I just add zero.
>>>
>>> ?null+0
>>> Null
>>>
>>> Confused...?
>>
>> I don't think I am.
>> I never tried to /use/ such values directly, butI always assign them to a
>> numeric variable
>>
>> numericvar = field.value + 0
>>
>> It's been quite a while, but as I recall, it always worked.
>
> The following
>
> Dim IntVal As Integer
> IntVal = Null + 0
>
> errors out on the 2nd statement with Run-time error '94', "Invalid use of
> Null"

However, this does seem to work (maybe it is what you meant)...

Dim IntVal As Integer
IntVal = "0" & Field.Value
Debug.Print CStr(IntVal)

Rick

Bob Butler · Joined: 04 Oct 2007 Posts: 1325

"Rick Rothstein (MVP - VB)" wrote in
message @TK2MSFTNGP05.phx.gbl

> However, this does seem to work (maybe it is what you meant)...
>
> Dim IntVal As Integer
> IntVal = "0" & Field.Value
> Debug.Print CStr(IntVal)

Try that where Field.Value is a negative number
IntVal = "0-1" ' type mismatch

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

Null Hi all, In my vb code I return a recordset from a sql database. This recordset contains a variety of fields with varing data types. Some are string and some are integers. As I return the recordset I use the Write command to output the data to a comma sepe

Passing Null Values as arguments I am trying to figure out exactly how to create a function that will accept a null value as an argument. I have tried using the optional keyword. I have tried using string and variant data types. Ultimately, I want to build functions similar to IsNull, Is

convert "" to NULL What is the best way to convert whitespace to NULL when inserting data into a SQL table? I have a file that I'm parsing parts of into a UDT. Sometimes, the fields I'm parsing out of the file are blank, so that field of the UDT is set to "". When I insert

ADOrs "Invalid Use of Null" "Invalid Use of Null" Error when null is found on this line: = Any tips on how to deal with this? Thanks, j ********Full 'construct a new ADOr

checking for null recordset value How do you check for a null value in a recordset field? *** Sent via Developersdex ***