Default Yes to Allow Zero Length String

[rmcompute]

When designing a database, the Allow Zero Length parameter always defaults to
No when creating a text field. Is there a way to default it to Yes?

 

[mscertified]

Why?
A text field that is not filled in has a value of Null and that is what your
SQL will test for.

-Dorian

 

[rmcompute]

When I download data from SQL Server using DTS, some of the text fields have
null values and they cause errors during download. I am required to go into
each text field on the table and change the Allow Zero Length parameter to
Yes. It would be easier to default it to Yes during table design if that
were possible.

 

[George Nicholson]

Afaik, currently (Access 2003) I believe the default for that property *is*
Yes, and a lot of requests were made to change the default to No. I'm
guessing those requests were granted and you are using 2007?

In any case, here is a routine (credited to Allen Browne) that will change
the ZeroLengthString property of all text fields in all tables to No. You
can easily change "Const conPropValue = False" to True and it will "Fix" it
your way.

If you only want to apply this change to certain tables you can insert a
Select Case before "For Each fld In tdf.Fields"

some of the text fields have
null values and they cause errors during download. I am required to go
into
each text field on the table and change the Allow Zero Length parameter to
Yes.

Well, then they aren't Null values. They are zero length strings. Those are
*NOT* the same thing.

HTH,



Private Sub FixZLS()
' Set "Allow zero length string" property to NO for all fields in db
' from http://allenbrowne.com/bug-09.html (July 2007)

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Const conPropName = "AllowZeroLength"
Const conPropValue = False
Dim i As Integer

Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If Len(tdf.Connect) = 0 Then
If (tdf.Attributes And dbSystemObject) = 0 Then
Debug.Print tdf.Name
For Each fld In tdf.Fields
If fld.Properties(conPropName) Then
Debug.Print tdf.Name & "." & fld.Name
fld.Properties(conPropName) = False
i = i + 1
End If
Next
End If
End If
Next

MsgBox i & " fields modified."

Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

 

[rmcompute]

Thank you.

Afaik, currently (Access 2003) I believe the default for that property *is*
Yes, and a lot of requests were made to change the default to No. I'm
guessing those requests were granted and you are using 2007?

In any case, here is a routine (credited to Allen Browne) that will change
the ZeroLengthString property of all text fields in all tables to No. You
can easily change "Const conPropValue = False" to True and it will "Fix" it
your way.

If you only want to apply this change to certain tables you can insert a
Select Case before "For Each fld In tdf.Fields"


Well, then they aren't Null values. They are zero length strings. Those are
*NOT* the same thing.

HTH,



Private Sub FixZLS()
' Set "Allow zero length string" property to NO for all fields in db
' from http://allenbrowne.com/bug-09.html (July 2007)

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Const conPropName = "AllowZeroLength"
Const conPropValue = False
Dim i As Integer

Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If Len(tdf.Connect) = 0 Then
If (tdf.Attributes And dbSystemObject) = 0 Then
Debug.Print tdf.Name
For Each fld In tdf.Fields
If fld.Properties(conPropName) Then
Debug.Print tdf.Name & "." & fld.Name
fld.Properties(conPropName) = False
i = i + 1
End If
Next
End If
End If
Next

MsgBox i & " fields modified."

Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub