Duplicates that have no value (1 Viewer)

Users can't tell the difference between spaces, nulls, and ZLS since all are invisible. I think the issue arises if they type something in and then either backspace to remove the visible characters or just type spaces to remove them. using the Del key removes the characters and sets the value of the control to null as does the Esc key. Except the Esc key reverts to the previous value.

Pat Hartman said:

Fields that are part of a compound unique index should NEVER be allowed to be null. Personally i prefer to not deal with ZLS either so I also don't allow ZLS in any text fields. If I want customerName to be required, then "" should NOT be valid.

Click to expand...

NEVER say NOT. In all my years of this I only came up against one case where I couldn't figure out an alternative.

I have photographic transparency records from an old hand written list. The number format is Year - SubjectCategory - Number
In numbering, sometimes I inadvertently reused numbers in a category. Rather than go back and alter a huge hand written list in every affected category, I added a suffix (e.g. B). So I might have 81-P-101 and 81-P-101-A. Years later I want to put the records and image links in a db. To ensure there are no duplicate records requires a composite index. Obviously I cannot enforce a suffix where there isn't one. To prevent dupes requires that I store zls in the suffix field, not Null. Things are not always so cut and dried.

With regard to the issue I mentioned, I ended up using datevalue of zero for an "any date", but then had to test for the special case of zero. Instead of users then seeing a blank for the "required date", they see a 1899 date, which was not ideal.

@Micron,
If you have to accommodate old, bad data, you might have to do something less than optimum but you should never plan on designing something that way from scratch. I think you would probably agree that allowing ZLS in a field like company name is just wrong. Unfortunately, the PTB at MS set AllowZLS to default to yes (in recent versions) which allows fields which should have values to be "empty". A solution which MS would probably not adopt is to prevent Required = Yes and Allow ZLS =Yes to be both true since that makes no sense.

I think you would probably agree that allowing ZLS in a field like company name is just wrong.

Click to expand...

I do and you are correct that I would not design from scratch that way. Having had the experience, I'd NEVER say NOT now.