Results 1 to 11 of 11

Thread: NZ (2000 SR-1)

  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    NZ (2000 SR-1)

    I think this is Access 101 but I cannot remember...

    I have a table with many colums and even more rows... I need to know if there is a way to change ALL the nulls that exist in the table to zeros easily. I know I can do an update query on each field but that could take some time.

    any tips?!
    There is always a way.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: NZ (2000 SR-1)

    What tyou could do is to write some VBA code to loop thru all the fields in the table and create an Update query and execute it.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NZ (2000 SR-1)

    Thanks for the vote of confidence but I am certain I cannot write that code <img src=/S/nope.gif border=0 alt=nope width=15 height=15>
    There is always a way.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NZ (2000 SR-1)

    Here is code that will replace all null (empty) values in all numeric fields in a table by zeros. To get it into your database:

    Activate the "Modules" tab of the database window.
    Click "New"
    Copy the following code and paste it into the module window:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub NullsToZeros(TableName As String)
    ' Declare variables
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String

    On Error GoTo ErrHandler

    ' Turn mouse pointer into hourglass
    DoCmd.Hourglass True

    ' Set reference to database and table
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(TableName)

    ' Loop through fields
    For Each fld In tdf.Fields
    Select Case fld.Type
    ' Handle numeric and currency types only
    Case dbByte, dbInteger, dbLong, dbSingle, dbDouble, dbCurrency
    ' Assemble SQL instruction for update query
    strSQL = "UPDATE [" & TableName & "] SET [" & fld.Name & "] = 0 " & _
    "WHERE [" & fld.Name & "] Is Null"
    CurrentDb.Execute strSQL, dbFailOnError
    Case Else
    ' Skip all other field types
    End Select
    Next fld

    ExitHandler:
    ' Clean up
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    ' Return mouse pointer to normal
    DoCmd.Hourglass False
    Exit Sub

    ErrHandler:
    ' Notify user
    MsgBox Err.Description, vbExclamation
    ' Always clean up
    Resume ExitHandler
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

    You need to set a reference to the DAO library, otherwise the code won't run:
    Select Tools | References...
    Locate "Microsoft DAO 3.6 Object Library" in the list.
    Make sure that the corresponding check box is ticked.
    Click OK.

    To use this code, do the following:
    Activate the Immediate window (Ctrl+G).
    Type NullsToZeros "tablename" then press Enter, where tablename is the name of the table in which you want to replace empty values by zeros. The name of the table must be surrounded by double quotes. For example:

    NullsToZeros "tblMyData"

    Wait until the mouse pointer changes from an hourglass to the standard pointer.

    Finally, switch back to Access (using the windows Task Bar) to verify that your table has been updated correctly. If you ever want to re-use this code, save the module.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NZ (2000 SR-1)

    This is exactly why there is no better place on EARTH than Woody's Lounge!

    It worked perfectly and you saved me days of work.

    THANKS!!!
    There is always a way.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NZ (2003 SR2)

    In July 2003 Hanz gave me some great code that replaced all the NULLS in a table with zeros very quickly.

    I came back to use that code again only to realize that it will not work in my current MS Access 2003 SP2.

    Can someone offer assistance with converting that code below to work in my new version of Access???

    Thanks!!!
    There is always a way.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NZ (2000 SR-1)

    The code should work in Access 2003 as well. Make sure that you have set a reference to the Microsoft DAO 3.6 Object Library, as explained in my previous reply.

    If it doesn't work, do you get an error message? If so, what does it say?

    Regards,
    Hans (not Hanz) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NZ (2000 SR-1)

    I got...
    Compile Error:
    Expected variable or procedure, not module
    There is always a way.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NZ (2000 SR-1)

    The procedure (sub) I posted is named NullsToZeros. Make sure that you don't have a module of the same name. If you give a module the same name as a procedure or function, Visual Basic becomes confused. Name the module basNullsToZeros instead, for example.

  10. #10
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NZ (2000 SR-1)

    THAT WAS IT!

    3 years later and you still come to the rescue!

    Thanks!
    There is always a way.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NZ (2000 SR-1)

    I haven't been away... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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