Replace Blank fields with 0 (1 Viewer)

Ben_Entrew

Registered User.
Local time
Today, 02:12
Joined
Dec 3, 2013
Messages
177
Hi all,
I try to go throgh all columns and replace all blank fields with 0.

Somehow this doesn't work:

Code:
Sub TEST()
Dim str As String
Dim rs As DAO.Recordset
Dim fld As DAO.Field


str = "Select * from Quantity_022014"

Set rs = CurrentDb.OpenRecordset(str)

For Each fld In rs.Fields
    On Error Resume Next
    
    If fld.Value = Null Then
    
    fld.Value = 0
    
    End If

Next fld
End Sub

Can someone help me out here?

Thanks in advance.

Regards,
Ben
 

bob fitz

AWF VIP
Local time
Today, 09:12
Joined
May 23, 2011
Messages
4,717
Have you tried creating an Update query to make the required changes.
 

Ben_Entrew

Registered User.
Local time
Today, 02:12
Joined
Dec 3, 2013
Messages
177
Thanks for the hint,

the problem is that I got variable column names.
I need to tell the column names each by each in an UPDATE query, am I wrong?

Regards,
Ben
 

Ben_Entrew

Registered User.
Local time
Today, 02:12
Joined
Dec 3, 2013
Messages
177
The column names depend on the reporting months,

e.g. 012014.

Furthermore some columns can be added dynamically depending on the selection of the user.

Is there a way to loop through all columns and replace all blank fields of column with 0 ?

Regards,
Ben
 

bob fitz

AWF VIP
Local time
Today, 09:12
Joined
May 23, 2011
Messages
4,717
try changing:
If fld.Value = Null Then
to:
If IsNull(fld.Value) Then
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:12
Joined
Aug 30, 2003
Messages
36,118
My untested assumption is that you'd have to refer to the recordset and use the Edit method

Code:
If IsNull(rs(fld.Name)) Then
  rs.Edit
  rs(fld.Name) = 0
  rs.Update
End If

You're also going to have to loop the recordset to check every record.
 

Ben_Entrew

Registered User.
Local time
Today, 02:12
Joined
Dec 3, 2013
Messages
177
Hi all,

thanks for your help.

I found this solution in the web:

Code:
Sub TEST()

    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim rs  As DAO.Recordset
    Dim i As Integer
   
    Set rs = CurrentDb.OpenRecordset("Quantity_022014")
    Do Until rs.EOF = True
        rs.Edit
        For i = 0 To rs.Fields.Count - 1
                If IsNull(rs(i).Value) Then
                rs(i) = 0
            End If
        Next
        rs.Update
        rs.MoveNext
    Loop
    Set tdf = Nothing
End Sub


This works fine.

Regards,
Ben
 
Last edited:

Users who are viewing this thread

Top Bottom