[RESOLVED] How i can Replace Filed Null to 0 in select Statement

[Ami_Tech]

How i can Replace Filed Null to 0 in select Statement
as :
"Select ID,Name," & _
"Tel From TB1 Order BY ID "

Tel : maybe Null " "

in VB6

[dilettante]

You could fix the database after replacing all NULL values by 0. Disallow NULL and make the DEFAULT = 0.

Or you can use IIf() in your SELECT to detect IsNull(column name) and return a 0 instead of its value.

[Ami_Tech]

thx
Can you edit
Select Statement

"Select ID,Name," & _
"Tel From TB1 Order BY ID "

[Elroy]

Others won't like this, but I wrap every read-reference to my database fields with something similar to the following:

Public Function DoubleVal(fld As Field, Optional dDefaultForNull As Double = 0) As Double
    If IsNull(fld) Then
        DoubleVal = dDefaultForNull
    Else
        DoubleVal = fld
    End If
End Function

Public Function SingleVal(fld As Field, Optional dDefaultForNull As Single = 0) As Single
    If IsNull(fld) Then
        SingleVal = dDefaultForNull
    Else
        SingleVal = fld
    End If
End Function

That completely solves the problem for me. I've got a somewhat long list of similar functions.

Good Luck,
Elroy

[Ami_Tech]

thx Elroy
but i need edit the Sql Statement :
"Select ID,Name," & _
"Tel From TB1 Order BY ID "

[ColinE66]

Like dil says, the real solution is to properly design (and define) your database up-front. Personally, I don't see the need for functions such as those that Elroy posted if the design is correct at the outset. And, even if it isn't, you can 'patch' existing databases to meet your design after the event (in simple cases such as yours) by modifying the table. That is the way to go, in my opinion.

[ChrisE]

Others won't like this, but I wrap every read-reference to my database fields with something similar to the following:

Public Function DoubleVal(fld As Field, Optional dDefaultForNull As Double = 0) As Double
    If IsNull(fld) Then
        DoubleVal = dDefaultForNull
    Else
        DoubleVal = fld
    End If
End Function

Public Function SingleVal(fld As Field, Optional dDefaultForNull As Single = 0) As Single
    If IsNull(fld) Then
        SingleVal = dDefaultForNull
    Else
        SingleVal = fld
    End If
End Function

That completely solves the problem for me. I've got a somewhat long list of similar functions.

Good Luck,
Elroy

Hi Elroy,

I do it more or less the same way...

'liefert einen Numerischen Wert als Variant
Public Function GetNum(Rs As ADODB.Recordset, _
                       FieldName As String, _
                       Optional DefaultIfIsNull As Variant = 0) _
                       As Variant
      If IsNull(Rs.Fields(FieldName).Value) Then
         GetNum = DefaultIfIsNull
      Else
         GetNum = Rs.Fields(FieldName).Value
      End If
End Function

'liefert einen numerischen Wert als String
Public Function GetNumStr(Rs As ADODB.Recordset, _
                          FieldName As String, _
                          Optional DefaultIfIsNull As _
                          String = vbNullString, _
                          Optional sFormat As String = _
                          vbNullString) As String
   Dim s As String
      If IsNull(Rs.Fields(FieldName).Value) Then
         s = DefaultIfIsNull
      Else
         s = Rs.Fields(FieldName).Value
      End If
      
      If Len(sFormat) > 0 Then
         s = Format(s, sFormat)
      End If
      GetNumStr = s
End Function

regards
Chris

[westconn1]

depends on the database type

see https://www.w3schools.com/sql/sql_isnull.asp for examples

[IkkeEnGij]

Tel : maybe Null " "

a space is definitely not a NULL

[Ami_Tech]

Yes Yes Yes
Thank you very very very Much
The problem has been successfully resolved
thank you all