Allow Zero length string

[Guest]

In my app I was keen to set allow zero length string to No, because I was
under the impression that it would be safer, or less likely to cause errors.

I have a form that records maintenance works done based on table (part of
table shown below), When completed maintenance work is recorded for an item,
the record for that item is marked as updated.

At the same time I use paste append to make a new copy of the item details
in the table with new condition, new assessment date, new work required etc.


Item name
Item location
Item description
Item condition
Date of assessment
Work required to bring up to standard
Description of Maintenance completed
Date completed
Item new condition
Record updated


If I set allow zero length string to “Noâ€, the paste append process won’t
run, due to some records having null values in some fields.
If I set allow zero length string to “Yesâ€, the paste append works well.

The question is: Will setting allow zero length strings to “Yes†cause
problems with the app in the future for the users?

 

[Brendan Reynolds]

My preference would be to write code to create the new record, rather than
using Paste Append, when this would cease to be an issue. However, if you
really want to change the Allow Zero Length property, the important thing to
remember is that you need to ensure that any code or expression that tests
the value of those fields for Null must be modified to test for either Null
or a zero-length string.

Zero-length strings are not necessarily any less safe or any more error
prone than Null values, if used with care. You just have to remember that a)
you now have two possible 'empty' values that, although they are
indistinguishable to the user, are very different, and b) allowing
zero-length strings is not an alternative to dealing with Null values, which
will still occur in number and date fields.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Arvin Meyer]

A ZLS is not quite the same as a NULL. An empty string in a field means
there is a value but I don't know what it is. A NULL means that I have no
idea whether there is a value or not. The difference seems subtle, but it
isn't.

Obviously, you need to accept ZLS (or NULLs) if it's in the data that you
are appending. It shouldn't cause a problem unless the data is required for
a record due to business rules or the record's integrity. In that case you
either supply it or you don't allow the record.

What you can do is to use the BeforeUpdate event of the form to check for
the existence of a value in a control and deal with it before writing to the
tables. Now you can have your cake and eat it too. <g> Try something like
this:

Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.txtWhatever & vbNullString) = 0 Then
MsgBox "Fix me - I need a value"
Cancel = True
Me.txtWhatever.SetFocus
End If
End Sub

What that does is to check for no value in txtWhatever and deal with it by
supplying a value. Then it puts the user back to where he/she can fix it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thanks

My preference would be to write code to create the new record, rather than
using Paste Append, when this would cease to be an issue.

Can you point me to where I can learn what to write in the code please.

Arvin wrote:
Obviously, you need to accept ZLS (or NULLs) if it's in the data that you
are appending. It shouldn't cause a problem unless the data is required for
a record due to business rules or the record's integrity.
The null fields are not required for business reasons, I feel relieved to
know that its not likely to be an issue as I can handle nulls for fields
where nulls not acceptable.

 

[Brendan Reynolds]

Well, the details will depend very much on your forms, and your tables. But
something like ...

Private Sub Command8_Click()

Me.RecordsetClone.AddNew

'This will leave the field Null if the control contains
'either a zero-length string or a Null value.
If Len(Me.txtTestDate And vbNullString) > 0 Then
Me.RecordsetClone.Fields("TheDate") = Me.txtTestDate
End If
If Len(Me.txtTestText & vbNullString) > 0 Then
Me.RecordsetClone.Fields("TheText") = Me.txtTestText
End If
Me.RecordsetClone.Update
Me.Bookmark = Me.RecordsetClone.LastModified

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.