[RESOLVED] How i can Replace Filed Null to 0 in select Statement-VBForums
Results 1 to 10 of 10

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

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    27

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

    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

  2. #2
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,735

    Re: How i can Replace Filed Null to 0 in select Statement

    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.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    27

    Re: How i can Replace Filed Null to 0 in select Statement

    thx
    Can you edit
    Select Statement

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

  4. #4
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    3,145

    Re: How i can Replace Filed Null to 0 in select Statement

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

    Code:
    
    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

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    27

    Re: How i can Replace Filed Null to 0 in select Statement

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

  6. #6
    Fanatic Member
    Join Date
    Apr 2012
    Posts
    883

    Re: How i can Replace Filed Null to 0 in select Statement

    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.
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  7. #7
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    665

    Re: How i can Replace Filed Null to 0 in select Statement

    Quote Originally Posted by Elroy View Post
    Others won't like this, but I wrap every read-reference to my database fields with something similar to the following:

    Code:
    
    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...
    Code:
    '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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,969

    Re: How i can Replace Filed Null to 0 in select Statement

    depends on the database type

    see https://www.w3schools.com/sql/sql_isnull.asp for examples
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9
    Fanatic Member
    Join Date
    Jun 2014
    Posts
    993

    Re: How i can Replace Filed Null to 0 in select Statement

    Quote Originally Posted by Ami_Tech View Post
    Tel : maybe Null " "
    a space is definitely not a NULL
    do not put off till tomorrow what you can put off forever

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    27

    Re: How i can Replace Filed Null to 0 in select Statement

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.