Changing all ZLS in a table to Null

[Stormin]

Hello,

I have an auto-generated report in .xls format that I import daily into my 2010 database. In some rare circumstances there is data missing from fields (empty cells) which, when imported, are Zero Length Strings in Access. I have everything in my database set to disallow ZLS since I use Null as an indicator of empty values (see here).

However, when I import the Excel file the entire records that contain at least one ZLS are not imported. This is expected.

e.g. if I imported the table below, only the first record will be imported.

Field1   Field2   Field3
Name1    Place1   Weight1
Name2    Place2   
Name3             Weight3

I instead would like these records to be imported and the ZLS replaced with Null. So I will have a temporary table that allows ZLS, then run an UPDATE query to change all "" to Null.

I have 31 fields that are being imported and ZLS can potentially appear in most of them. Is there an easy way to replace the same values across the entire table? Previously I have had to run a separate query for each field; luckily the most I've had to do is 3.

Thanks

 

[JANR]

Set the property "AllowZeroLength" of the field(s) in your table to No.

To do it to all your tables se this: http://allenbrowne.com/bug-09.html

good luck

Jan R

 

[Stormin]

Hi JANR,

This is already done and is the reason the records are not imported.
You have posted the same webpage as was linked in my original post

 

[isladogs]

You could try a different approach
Set allow ZLS to Yes in the tables you are importing to
Import the data then convert any ZLS to null

Might be worth trying

 

[Stormin]

Hi ridders,

That is exactly what I described in my original post.

My question is the method in which to run an update query on all fields.


I feel like nobody has actually read my original post, lol

 

[isladogs]

Hi

So you did....
Sorry us old timers have a short attention span.
Must have drifted off for a second or ten ...

Anyway, I'm fairly sure I have what you need somewhere if I can find it
In the meantime, suggest you try to write a routine to loop through the various fields in turn, running an adapted version of your query using [" & strFieldName & "] notation instead of a specific field name. It would look something like this:

CurrentDB.Execute "INSERT INTO tblY9ChoicesTEMP ( PupilID, SubjectID, OptionLine )" & _
                " SELECT CStr(Y9ChoicesIMPORT.AdNo) AS PupilID, Y9ChoicesIMPORT.[" & strFieldName & "] AS SubjectID, '" & strBlockName & "' AS OptionLine" & _
                " FROM Y9ChoicesIMPORT" & _
                " WHERE (((Y9ChoicesIMPORT.[" & strFieldName & "])<>''));"

One of the following functions may be of use in getting the field names from your table:

Public Function GetFieldList(ByVal strTable As String) As String

'#############################
'Lists all fields in a table
'#############################

'Const cSep As String = ";"
'Const cQuote As String = """"

Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strTemp As String

Set DB = CurrentDb
Set tdf = DB.TableDefs(strTable)
For Each fld In tdf.Fields
If Len(strTemp) = 0 Then
'strTemp = cQuote & fld.Name & cQuote
    strTemp = fld.Name
Else
'strTemp = strTemp & cSep & cQuote & fld.Name & cQuote
    strTemp = strTemp & ", " & fld.Name
End If
Next fld

GetFieldList = strTemp
'Debug.Print GetFieldList

Set tdf = Nothing
Set DB = Nothing
End Function

==================================================

Public Function CheckFieldExists(ByVal TableName As String, ByVal FieldName As String) As Boolean

'#############################
'Checks if specified field exists in a table
'#############################

Dim nLen As Long

On Error GoTo Err_Handler

With DBEngine(0)(0).TableDefs(TableName)
    .Fields.Refresh
    nLen = Len(.Fields(FieldName).Name)

    If nLen > 0 Then CheckFieldExists = True

End With

Exit_Handler:
    Exit Function
    
Err_Handler:
    If Err.Number <> 3265 Then ' Error 3265 : Item not found in this collection.
        MsgBox "Error " & Err.Number & " in CheckFieldExists procedure : " & Err.Description
    End If
    CheckFieldExists = False
    
    GoTo Exit_Handler
    
End Function

 

[Stormin]

Thanks ridders, very helpful. From your post and some experimentation I have built the following code:

Option Explicit

Public Function ZLStoNull(strTableName As String)
'Runs the ZLStoNull SQL on all text fields in a table
    
    Dim db      As Database
    Dim tdf     As DAO.TableDef
    Dim fld     As DAO.Field
    Dim strSQL  As String
    
    Set db = CurrentDb
    Set tdf = db.TableDefs(strTableName)
    
    For Each fld In tdf.Fields
        If fld.Type = 10 Then '10 = Text
            strSQL = _
                "UPDATE [" & strTableName & "] SET [" & strTableName & "].[" & fld.Name & "] = Null " & _
                "WHERE [" & strTableName & "].[" & fld.Name & "] = """";"
            CurrentDb.Execute strSQL
        End If
    Next fld
    
    Set tdf = Nothing
    Set fld = Nothing
    
End Function

It's working on a test table. I will implement it to my database tomorrow and report the results.

 

[isladogs]

Great.

Two things:
1. You should exclude fields that shouldn't be included in the update
e.g. PK field
2. Add error handling before testing on live data
You should be able to determine likely errors by playing with test data

Look forward to hearing how it goes

 

[Stormin]

Just a note that testing and integration to the live DB was completed problem-free. I didn't add code to exclude any fields since in my specific application it is not required (indeed, if the PKey has a ZLS in the data there is a different problem entirely!).

Some new knowledge:

• "fld.Type = 10" can be changed to "fld.Type = dbText" for the same effect (see here)
• "CurrentDb.Execute strSQL" was changed to "CurrentDb.Execute strSQL, dbFailOnError" which rolls back all changes in that execution instance if an error occurs with locked records etc. (see here)

 

[The_Doc_Man]

The method using .Execute works fine as long as you completely fill the SQL string with all substitutions before you send it to .Execute. For example, you cannot reference a value available only in the Access workspace, such as a form's text box contents. You have to actually pick up those contents and build them into the string.

The use of dbText might require a specific library reference because (if I recall this correctly) the data type names are in a segment that comes from the DAO library.

 

[Stormin]

Thanks for the extra info Doc.

dbText worked for me with the bog-standard Access 2010 installation. I need my databases to run on colleagues machines with the same installation (without fiddling about) so I tend to use CreateObject() instead of adding libraries.