empty string replace IsNull 1

[matrixindicator]

I need to replace an empty string "" to a null (IsNull) value in a table.
First I need to dedect an invisible empty string. How can I set an empty string for test and is this the best way to detect Nz(rst(x).Value, "") = "" an empty string ?

Public Sub StringIsNull()
Dim dbs As DAO.Database
Dim tdf As TableDef
Dim fld As Field
Dim x As Integer
Dim y As Integer
Dim rst As DAO.Recordset
y = 0
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("T01")
Set rst = dbs.OpenRecordset("T01", dbOpenDynaset)
rst.MoveFirst
 
For x = 0 To tdf.Fields.Count - 1
    Do While Not rst.EOF
    If Nz(rst(x).Value, "") = "" Then
        y = y + 1
    End If
    rst.MoveNext
    Loop
    
Next x

 

[traingamer]

No. That will detect null fields and zero length fields. See Distinguish between Null values and zero-length strings (MDB) in the included help files for one way to determine.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

But the easiest way is to simply check

If rst(x).Value = "" Then

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[TheAceMan1]

How are ya matrixindicator . . .

Try this:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, fld As Field
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("T01", dbOpenDynaset)
   
   Do
      For Each fld In rst.Fields
         If fld = "" Then
            rst.Edit
            rst(fld.Name) = Null
            rst.Update
         End If
      Next
      
      rst.MoveNext
   Loop Until rst.EOF[/blue]

It directly updates all null strings ([blue]""[/blue]) to [blue]Null![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[matrixindicator]

txs AceMan1 !

your code is fast, shorter better written and it works !
I have tested it by typing an empty string "" in an empty field and asked to show a message box when he passed there.

Just a little adaption required,

tbl as DAO.Field

 

[PWise]

try
for even faster code

Dim db As DAO.Database, rst As DAO.Recordset, fld As Field
Set db = CurrentDb
For Each fld In rst.Fields
db.execute "update T01 set " & fld.Name & "=null where " & fld.Name & "=''"        
Next