default Allow Zero Length

[Anne]

When I design a table, a new text field has always the 'Allow Zero Length'
field property set to 'Yes'.
Is it possible to have it as default set to 'No'

Thanks in advance,
Anne

 

[Allen Browne]

Like you, Anne, I really wish there was some way to turn AZL off by default.

It used to be off in earlier versions of Access, and I can't think of any
good reason why it should be on by default. That's even more so, because
Access itself does not handle the distinction between a zero-length-string
and a Null correctly: DLookup wrongly returns Null when it looks up a field
that contains a ZLS.

The best I can offer you is a piece of code that visits all the non-system
tables in your database, and turns ZLS off. Trouble is that you have to run
the code again every time you add tables or fields. Hope it's some
consolation:

Function FixZLS()
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

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

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

 

[Anne]

Thanks Allen,
It works, but as you already pointed out, it would be better to have it set
when I make a new field.

So if anyone knows a better solution........

Thanks Anne