Convert zero-length string or blank cell to null

[Ricardo]

Hi,

I know this is not the done thing, as generally if you know that no data
exists for a cell, you should always use a zero-length string to identify it
as empty.

HOWEVER. I do need NULL values in all cells that are blank/zero-length.

Is there anyway for me to take a whole table or failing that, a field and
convert all the blank (zero-length) cells into NULL. Possibly by using an
update query with code or the correct functions?

The reason why is that we are having a problem filtering as not all the
cells in one field are NULL, and using the Is Not Null function does not work
on all cells. I know that we're able to use the <>"" function to filter out
blanks but this is only a temporary fix, as we don't always use queries to
access data from a table but use the table itself, right click filter by
including or excluding specific data.

Any assistance on whether this is possible or not would be great.

Kind Regards.

 

[Tom Wickerath]

Hi Ricardo,

I know this is not the done thing, as generally if you know that no data
exists for a cell, you should always use a zero-length string to identify it
as empty.

Always? I do not agree. In fact, I routinely set the Allow Zero Lenght
property to No for all text and memo fields.

http://allenbrowne.com/bug-09.html
See "Fields: Allow Zero Length"

You can use an update query to update a text field with zero length strings
to null. Simply use Update To: Null with a criteria of ="". You need to do
this on a field-by-field basis, unless you want to write some code to walk
through all the text fields in a table. For that matter, you could probably
add the appropriate update statement quite easily to Allen's code available
at the link shown above.

The reason why is that we are having a problem filtering as not all the
cells in one field are NULL, and using the Is Not Null function does not work
on all cells.

In a query, you can test for the length, using a criteria like this:

Len ([FieldName] & "") = 0

where FieldName is the name of the field. This should give you all records
with ZLS or Nulls, but it won't help you with your table filtering.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Ricardo]

Fantastic Tom, that worked perfectly and is exactly the answer I required.
Although in regards to adding the update task to code and automating it for
ease of use, I was unable to decipher it. I will continue to do it field by
field as it's only two tables.

Thanks again for your help.

Regards

Ricardo

Hi Ricardo,

I know this is not the done thing, as generally if you know that no data
exists for a cell, you should always use a zero-length string to identify it
as empty.

Always? I do not agree. In fact, I routinely set the Allow Zero Lenght
property to No for all text and memo fields.

http://allenbrowne.com/bug-09.html
See "Fields: Allow Zero Length"

You can use an update query to update a text field with zero length strings
to null. Simply use Update To: Null with a criteria of ="". You need to do
this on a field-by-field basis, unless you want to write some code to walk
through all the text fields in a table. For that matter, you could probably
add the appropriate update statement quite easily to Allen's code available
at the link shown above.

The reason why is that we are having a problem filtering as not all the
cells in one field are NULL, and using the Is Not Null function does not work
on all cells.

In a query, you can test for the length, using a criteria like this:

Len ([FieldName] & "") = 0

where FieldName is the name of the field. This should give you all records
with ZLS or Nulls, but it won't help you with your table filtering.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Hi,

I know this is not the done thing, as generally if you know that no data
exists for a cell, you should always use a zero-length string to identify it
as empty.

HOWEVER. I do need NULL values in all cells that are blank/zero-length.

Is there anyway for me to take a whole table or failing that, a field and
convert all the blank (zero-length) cells into NULL. Possibly by using an
update query with code or the correct functions?

The reason why is that we are having a problem filtering as not all the
cells in one field are NULL, and using the Is Not Null function does not work
on all cells. I know that we're able to use the <>"" function to filter out
blanks but this is only a temporary fix, as we don't always use queries to
access data from a table but use the table itself, right click filter by
including or excluding specific data.

Any assistance on whether this is possible or not would be great.

Kind Regards.

 

[Tom Wickerath]

Hi Ricardo,

Although in regards to adding the update task to code and automating it for
ease of use, I was unable to decipher it. I will continue to do it field by
field as it's only two tables.

You might try this modification....but fair warning: I have only tested it
minimally. Of course, you should make a backup of your database before
running code like this.

Option Compare Database
Option Explicit

Function FixZLS()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim strSQL As String
Const conPropName = "AllowZeroLength"
Const conPropValue = False

On Error GoTo ProcError

Set db = CurrentDb()

For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
If tdf.Name <> "Switchboard Items" Then
For Each fld In tdf.Fields
If fld.Properties(conPropName) Then
Debug.Print tdf.Name & "." & fld.Name
fld.Properties(conPropName) = conPropValue

strSQL = "UPDATE [" & tdf.Name & "] SET [" _
& fld.Name & "] = Null " _
& "WHERE [" & fld.Name & "] ="""""
Debug.Print strSQL
db.Execute strSQL
Debug.Print db.RecordsAffected & " records updated."
Debug.Print
DoEvents

End If
Next
End If
End If
Next

MsgBox "Finished Fixing ZLS Fields.", vbInformation, "Fix ZLS Procedure"

ExitProc:
'Cleanup
Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure FixZLS..."
Resume ExitProc
End Function




Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________