VBA to set Null strings to ""

[jjwild]

I have a Jet database with many Null values in it. I want to convert these to "".

In looking at it, I think the easiest way to do this would be to have a routine like the following, if it is possible:

For each table in database.[table collection]:
For each row in the table:
For each field in the row:
if isnull(row.field):
if row.field.type equal to "Text":
Set row.field.value to "".
end if
end if
next
next
next

Does anyone have an example of something like this? or an idea where I can find one?

Also, I believe to declare a variable of type database, you have to include a certain reference. Can someone tell me what that reference is?

Thanks,


John Wildanger

 

[JeremyNYC]

John,

A solution built on that model will work...but you may be significantly older by the time it does.

Much faster would be to make an update query and just go through the fields one-by-one.

If there are tons of fields, try using this code:
[PLEASE back up your data before running this code or any other code you get from someone on the web]

Function NullToZLS(strTable As String)
On Error GoTo Error
Dim db As DAO.Database
Dim tdf As TableDef
Dim strSql As String
Dim strField As String
Dim intCount As Integer

Set db = CurrentDb
Set tdf = db.TableDefs(strTable)
For intCount = 0 To (tdf.Fields.Count - 1)
strSql = "UPDATE " & strTable & " SET " & tdf.Fields(intCount).Name & " = '' " _
& " WHERE " & tdf.Fields(intCount).Name & " Is Null"
Debug.Print strSql
Call db.Execute(strSql, dbFailOnError)
Debug.Print "Records Affected: " & db.RecordsAffected
Next intCount

Set tdf = Nothing
db.Close
Set db = Nothing

Exit Function

Error:
Select Case Err.Number
Case 3113 'Field not updateable--happens one autonumber fields.
Resume Next
Case Else
Call MsgBox(Err.Number & ": " & Err.Description)
End Select

End Function

Just put that in any standard module and do something like this in the immediate window:
call nulltozls("tblBank&quot

You'll get a nifty little print out of what happened.

Hope that does the trick for you.

Jeremy


==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[jjwild]

Jeremy-

Thanks for the data - I'll try it.

John

 

[RickSpr]

I may be mistaken, but I think assigning "" to a field will still leave it Null, unless you set the following field properties:
AllowZeroLength = True
Required = True

At least, I have found this to be true when attempting to store zero-length strings using a form.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[jjwild]

You are correct - AllowZeroLength must be True before you can set a text field to "". If it is False, you error out.

I have broadened what was posted above to do the following:

If field.type = 10 then (this is a text field)
field.AllowZeroLength = True
if field.Value is null then
field.Value = ""
end if
field.Required = True
field.DefaultValue = """""" (This is a string containing
two double quotes)
end if

This fully handles what I need done. There are maybe 200 such fields in this database.

Thanks.