Replacing Null Values in a table 1

[enbw]

Hi,

I'm fairly new to access. I was wondering if anybody could help me. I have several tables which need cleaning up before importing into another database. This requires all of the null values in each table to be replace with a character. I can achieve this through an update query on single fields but each table can have upto 60 fields. I was looking for a bit a generic code/method which could be applied to each table.

Thanks in advance.

 

[LonnieJohnson]

I would assume you are putting a generic value into these fields that have nulls. If so, then I would assume that these fields have a certain datatype because if your generix value was xx that would not go into a date or numeric field. Please elaborate, it would be helpful in writing code.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[Golom]

There is one bit of "generic" code you could try.

   UPDATE tbl Set Field1 = Nz(Field1), Field2 = Nz(Field2), ...

This just updates a field to itself if it isn't null or to some socially acceptable data value ("" for text, 0 for numeric) if it is. Doesn't solve the problem of dealing with all the fields though. You would still need to code them manually.

The other option is do it with code. Load everything into a recordset; iterate through the rows; on each row flip through the fields and set the field value to your default if it is NULL (select by field type to pick which default value to use.) This may be easier than trying to use SQL since (I assume) it's just a one-time thing.

 

[hwkranger]

if you want something generic, I would suggest having a record set, then looping through each field in that tabledef, and checking each field for each record for a null value and put something there.

structured like:

dim rst as dao.recordset
dim fld as field
dim db as dao.database

set db = currentdb()
'open your recordset
'while rst is not EOF then
'then do for each fld in rst whatever you want to do.

i'd write more, but it's saturday and I'm on my way to watch pitt vrs Miami.

Good luck.




Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[enbw]

Hi,

Thanks for the replies. I need to replace the null values with a space. I still not sure how to work the recordset example, is there any chance of further help?

Thanks

Brychan

 

[hwkranger]

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim tblName As String
Dim i As Integer

tblName = "YOUR_TABLE_NAME_HERE"

Set db = CurrentDb()
Set rst = db.OpenRecordset(tblName)

Exit Function
If rst.RecordCount Then
rst.MoveFirst
Do While Not rst.EOF
For i = 0 To rst.Fields.Count - 1
If IsNull(rst(i)) Then
rst.Edit
rst(i) = " "
rst.Update
End If
Next i
rst.MoveNext
Loop

End If

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[hwkranger]

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim tblName As String
Dim i As Integer

tblName = "YOUR_TABLE_NAME_HERE"

Set db = CurrentDb()
Set rst = db.OpenRecordset(tblName)

If rst.RecordCount Then
rst.MoveFirst
Do While Not rst.EOF
For i = 0 To rst.Fields.Count - 1
If IsNull(rst(i)) Then
rst.Edit
rst(i) = " "
rst.Update
End If
Next i
rst.MoveNext
Loop

End If


Sorry I put an "exit function" in the middle of the code. It was an exit point before I ran it (to make sure it worked)

heh.. I removed it - this will work for you.

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[enbw]

Hi,

The code works great, many thanks. I have been trying to use the type command to determine the type of field. I only want to update fields which are Null and of type text.

I have put in a condition rst(i).type = 10. But it doesn't seem to do the job. I believe the value 10 is text. Any further help would be appreciated.

Thanks to everyone.

Brychan

 

[enbw]

Hi,

Please ignore the my lastest reply, school boy error.

Brychan