how to update "null" value with zero ?? 1

[pdtit]

Hey,

I found out that I have a lot of "null" (=empty) values in my tables throughout my database.

Instead of going through each table and every field, I was wondering if there is a smart one out there who can tell me how I can run some piece of code to update all my fields where I have "Null" values, with a "0".

This should be done only once, consider it as some kind of maintenance on my database.

Peter

 

[lastout]

It's not clear to me why you want to exchange Null for zero. They are different but is there a particular use you have in mind for zero that null can't do just as well? If so, please describe.

The fields in your tables that contain null values -- are they all the same data type (all numbers) or are they different data types (text, numbers, dates, etc)?
I wouldn't be one to recommend setting up code to do anything like a one shot deal unless writing code was clearly simpler than doing it manually.

Can you describe your scenario in more detail? lastout (the game's not over till it's over)

 

[SequenceIT]

Firstly, make sure that the default values for all the fields in all the tables are set to some approriate value to avoid this happening again in the future.

Now, if the problem is just in dealing with the Nulls in data processing, you can alway use Nz to fix it up.
e.g. intMyInteger = Nz([MyField],"0&quot

If you *really* need to get rid of all the Nulls then use a procedure like the following:

'***************** CODE STARTS ********************
Public Sub ReplaceNulls(strTableName As String)
'*******************************************
'Name: AllNullToBlank (Sub)
'Purpose: Goes through the table replacing
' Nulls with empty strings or zeros.
' This will then allow comparison
' of these previously empty values.
' (Null <> Null but 0 = 0 and &quot;&quot; = &quot;&quot
'Author: Andrew Webster
' Sequence Information Technology
'Date: December 21, 2001, 06:00:00 PM
'Called by:
'Calls:
'Inputs: Table name as string
'Output:
'*******************************************


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim intRecordCount As Integer
Dim intCurrentRecord As Integer

On Error GoTo Err_Handler

Set db = CurrentDb
Set rs = db.OpenRecordset(strTableName, dbOpenDynaset)

'Set up user feedback
rs.MoveLast
intRecordCount = rs.RecordCount
rs.MoveFirst

SysCmd acSysCmdInitMeter, &quot;Checking for Null values...&quot;, intRecordCount
intCurrentRecord = 1

'Loop through the records
Do While Not rs.EOF
'Loop through the fields swapping nulls for &quot;&quot; or 0
For Each fld In rs.Fields
rs.Edit
'Set all empty text fields to&quot;&quot;
If fld.Type = dbText Then
fld.Value = Nz(fld.Value, &quot;&quot
Else
'Set all empty numeric fields to 0
fld.Value = (Nz(fld.Value, 0))
End If
rs.Update
Next fld
rs.MoveNext
SysCmd acSysCmdUpdateMeter, intCurrentRecord
intCurrentRecord = intCurrentRecord + 1
Loop

SysCmd acSysCmdRemoveMeter
Exit_Sub:
Set db = Nothing
Set rs = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description & &quot; (&quot; & Err.Number & &quot&quot;
Resume Exit_Sub

End Sub
'***************** CODE ENDS **********************

Note 1.: Write a routine that passes it all the tables that you want to change. N.B. DON'T do the system tables! Use a check like
If Instr(1, strMyTableName, &quot;sys&quot = 0 Then
ReplaceNulls strMyTableName
End If

Note 2.: You may wish to test more thoroughly for the type of field (e.g. memo, OLE etc) to ensure that you don't give the error handler any work to do!

Hope this sort you out. Andrew Webster MCSD

Sequence Information Technology

 

[suoirotciv]

SequenceIT reply is great

Actually i'm using the following code to change null to 0

variable = trim(textbox & &quot; &quot Victorious Manal
ePacific Global Contact Center
Location: Manila, Philippines
Years of Programming: 3 Years

 

[suoirotciv]

SequenceIT reply is great

Actually i'm using the following code to change null to 0

variable = trim(textbox & &quot; &quot

but next time i'm going to use SequenceIT code Victorious Manal
ePacific Global Contact Center
Location: Manila, Philippines
Years of Programming: 3 Years