Replacing Data

[mikesal57]

Hi All ...




First time here and hopefully for a while as I'm learning Access
I have a large table stored. Maybe 200 columns wide and 250,000 + rows.
I need at least , for now, 37 columns with 1's and 0's in it. I need to replace
the 0's with a blank....I tried "REPLACE" option but the process halts and doesn't finish.
I saw a video with making 2 queries for a REPLACE & UPDATE , but making 2 for each column
is too much.....Any solutions?
Thxs
Mike

[kd2017]

That's a lot of columns. I wonder if your data is properly normalized?

Anyway, maybe something like this?

Public Sub SetZerosToNull()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim column_names() As String
    Dim column_name As Variant
    Dim update_qry As String
    
    Set db = CurrentDb
    
    'REPLACE THESE COLUMN NAMES WITH YOUR ACTUAL COLUMN NAMES
    column_names = Split("[column1],[column2],[column3],[column4]", ",")
    
    For Each column_name In column_names
        'REPLACE THE TABLE_NAME_HERE WITH YOUR TABLE NAME
        update_qry = "UPDATE [table_name_here] SET " & column_name & " = null WHERE " & column_name & " = 0;"
        
        'Debug.Print update_qry
        db.Execute update_qry, dbFailOnError
    Next

ExitHandler:
    Set db = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub

[Welshgasman]

Cross posted https://utteraccess.com/topics/2060924?post=unread

[mikesal57]

That's a lot of columns. I wonder if your data is properly normalized?

Anyway, maybe something like this?

Public Sub SetZerosToNull()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim column_names() As String
    Dim column_name As Variant
    Dim update_qry As String
    
    Set db = CurrentDb
    
    'REPLACE THESE COLUMN NAMES WITH YOUR ACTUAL COLUMN NAMES
    column_names = Split("[column1],[column2],[column3],[column4]", ",")
    
    For Each column_name In column_names
        'REPLACE THE TABLE_NAME_HERE WITH YOUR TABLE NAME
        update_qry = "UPDATE [table_name_here] SET " & column_name & " = null WHERE " & column_name & " = 0;"
        
        'Debug.Print update_qry
        db.Execute update_qry, dbFailOnError
    Next

ExitHandler:
    Set db = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub

thank you so much ...will give it a shot ....just got to find out where VBA's go in Access

[moke123]

With 200 fields in a table your data is almost certainly not normalized.
Tables should be tall (many records) and thin ( Not 200 fields!)

What is the datatype of the fields you are trying to Null? Some datatypes are a P.I.T.A.

Have you tried looping through the table?

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
  
    strSql = "select * from  YoutTableName"  'change as appropriate
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)


    If rs.BOF And rs.EOF Then
        GoTo MyExit
    End If


    Do Until rs.EOF

        rs.Edit
        rs!YourFieldName = Null  'change as appropriate
        rs!YourFieldName = Null  'change as appropriate
        rs!YourFieldName = Null  'change as appropriate
        rs.Update

        rs.MoveNext
    Loop


MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing

[kd2017]

thank you so much ...will give it a shot ....just got to find out where VBA's go in Access

You're welcome. Press Alt-F11 and in the toolbar click on insert module -OR- in the ribbon go to Create -> Macros & Code -> Module

[mikesal57]

having an issue running it....

[mikesal57]

I get this now after I capitalized column headers...

[mikesal57]

add this to list

[kd2017]

That last one is very weird. That tells me that references aren't there that should be by default. As you can see in my debug window the code should at least compile. Beyond that if there's an issue with the code it's in the SQL statement.

Go to Tools -> References... and make sure at least these boxes are checked. If something's not there scroll down to find it, what's not checked will be in alphabetical order in the listbox (it's a long list).

[mikesal57]

That last one is very weird. That tells me that references aren't there that should be by default. As you can see in my debug window the code should at least compile. Beyond that if there's an issue with the code it's in the SQL statement.

Go to Tools -> References... and make sure at least these boxes are checked. If something's not there scroll down to find it, what's not checked will be in alphabetical order in the listbox (it's a long list).

got this...

[kd2017]

Add 'Microsoft Office 16.0 Access database engine Object Library' per my previous post.

[mikesal57]

Add 'Microsoft Office 16.0 Access database engine Object Library' per my previous post.

will KD...let you know what happens..

[kd2017]

FYI this is all assuming that your fields are numeric datatypes that accept null values. For example:

[mikesal57]

It worked somehow....but not from the macro I created...

Pictures of what happen when I rAn Macro....and.......pictures of when I RAN it from ALT-11 screen...