Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Replacing Null fields in Access Records column - VB6

  1. #1
    Join Date
    Aug 2002
    Posts
    6,223
    Rep Power
    0

    Default Replacing Null fields in Access Records column - VB6

    I have an Access database with a table that I have added a new column. Data has been added to that column since it has been created. Most, but not all fields in this column do not contain any data. I have tried creating a little program to insert the value "0" into the Null fields, but have had no success:

    Code:
    Private Sub Command1_Click()
    
    With Data1.Recordset
        .MoveLast
        .MoveFirst
        Do Until .EOF
            .Edit
            If .Fields(txtColumn) Is Null Then
                .Fields(txtColumn) = txtValue
                myCount = myCount + 1
            End If
            If .Fields(txtColumn) = "" Then
                .Fields(txtColumn) = txtValue
                myCount = myCount + 1
            End If
            If .Fields(txtColumn) = " " Then
                .Fields(txtColumn) = txtValue
                myCount = myCount + 1
            End If
            .Update
            .MoveNext
        Loop
    MsgBox (myCount & " of " & .RecordCount & " records modified.")
    End With
    End Sub
    Can anyone tell me where I am going wrong? (Yes I know about Select Case. The code will be simplified once I find out the problem).
    .
    PC - Ubuntu 15.04 64bit Desktop
    HP Pav G60-236US 3GB RAM Laptop, Ubuntu 15.04 64bit and Win7 Home

    "So Daddy, how come you telling me stealing not right when YOU copying DVDs? How come? How Come?"


    RIP Ramesh ...

  2. #2
    Join Date
    Feb 2005
    Posts
    85
    Rep Power
    0

    Default

    Try this:
    Code:
    if len(.Fields(txtColumn))=0 Then 'zero length values are null
            .Fields(txtColumn) = txtValue
            myCount = myCount + 1
    end if
    Code King aka Code WizZzard: Motivated By The Challenge, Driven By The Will To Succeed.

    In The Land Of Programmers, The Code WizZzard Is KING. Sen on anything VB

  3. #3
    Join Date
    Aug 2002
    Posts
    6,223
    Rep Power
    0

    Default

    Thanks. I'll try that.

    {Edit} It doesn't work. If I don't test for null it replaces all with the text and if I do it doesn't replace anything.

    This is the code as is now. I've replaced the variables with actual values:

    Code:
    Private Sub Command1_Click()
    
    myCount = 0
    With Data1.Recordset
        .MoveLast
        .MoveFirst
        Do Until .EOF
            .Edit
            If Len(.Fields("envfee")) = 0 Then 'zero length values are null
                .Fields("envfee") = 0
                myCount = myCount + 1
            End If
            .Update
            .MoveNext
        Loop
    MsgBox (myCount & " of " & .RecordCount & " records modified.")
    End With
    
    End Sub
    The result is "0 of 488 records modified." and no data is changed. {/Edit}
    .
    PC - Ubuntu 15.04 64bit Desktop
    HP Pav G60-236US 3GB RAM Laptop, Ubuntu 15.04 64bit and Win7 Home

    "So Daddy, how come you telling me stealing not right when YOU copying DVDs? How come? How Come?"


    RIP Ramesh ...

  4. #4
    Join Date
    Dec 2004
    Posts
    1,181
    Rep Power
    0

    Default

    You could also use the IsDBNull function. It takes an object and test if it is a database null. It returns a boolean value.
    'If we're supposed to work in Hex, why have we only got A fingers?'

    Follow Me: @psybuck2002us

  5. #5
    Join Date
    Aug 2002
    Posts
    6,223
    Rep Power
    0

    Default

    OK, got it now. psybuck2002us, I tried that function but apparently it is a VBNet function, not VB6. In VB6 the function would be IsNull. The code below now works.

    Code:
    Private Sub Command1_Click()
    Dim NullCheck As Boolean
    myCount = 0
    With Data1.Recordset
        .MoveLast
        .MoveFirst
        Do Until .EOF
            .Edit
            NullCheck = IsNull(.Fields("envfee"))
            If NullCheck Then
                .Fields("envfee") = 0
                myCount = myCount + 1
            End If
            .Update
            .MoveNext
        Loop
    MsgBox (myCount & " of " & .RecordCount & " records modified.")
    End With
    
    End Sub
    Result: 385 of 488 records modified.
    .
    PC - Ubuntu 15.04 64bit Desktop
    HP Pav G60-236US 3GB RAM Laptop, Ubuntu 15.04 64bit and Win7 Home

    "So Daddy, how come you telling me stealing not right when YOU copying DVDs? How come? How Come?"


    RIP Ramesh ...

  6. #6
    Join Date
    May 2003
    Posts
    108
    Rep Power
    0

    Default

    From MS Access you can run the following query instead of creating a program:
    UPDATE TableName SET ColumnName=0 WHERE ColumnName IS NULL
    www.pixJM.com - View and upload photos/videos of what's currently happening in the streets of Jamaica.

  7. #7
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default SQL could have accomplish this...

    Quote Originally Posted by fatta77 View Post
    From MS Access you can run the following query instead of creating a program:
    UPDATE TableName SET ColumnName=0 WHERE ColumnName IS NULL
    I was wondering why this wasn't done in the first place.

  8. #8
    Join Date
    Dec 2004
    Posts
    1,181
    Rep Power
    0

    Default

    Quote Originally Posted by codecarnage View Post
    I was wondering why this wasn't done in the first place.

    There are times when NULL database values represent a field better than just adding a zero or empty string.
    'If we're supposed to work in Hex, why have we only got A fingers?'

    Follow Me: @psybuck2002us

  9. #9
    Join Date
    Sep 2003
    Posts
    603
    Rep Power
    0

    Default

    I was wondering why a program was needed for this. set the default value for the column to "0", run the update query above and all is well. should not take more than 2 mins.
    easiparcel.com Shop online and ship to Jamaica

  10. #10
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default nothing wrong with what i said

    Quote Originally Posted by psybuck2002us View Post
    There are times when NULL database values represent a field better than just adding a zero or empty string.
    ..are you sure you didn't misread my post...fatta made a suggestion of using SQL to replace null...my follow up response was agreeing with him (fatta) by saying that (ramesh) should have used SQL for first attempt rather than writing a program...

    And i'm well aware of the relational of using Null values fields.
    Last edited by codecarnage; Dec 21, 2007 at 12:37 PM. Reason: grammar

Posting Permissions

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