Zero length Fields

[Guest]

I have a table that has 3 key fields - the third key field is potentially
blank. I can set the field to zero length and default values to "". But if
the user deletes the entries in the field on the form then when it goes to
save the record it assumes the value is null and will not add a null value to
a key field. Is there someway to bypass this. I have tried to figure out
what event this triggers. (form error does not fire when this happens, and
before insert does not fire, before update is too soon, and after update is
too late)

 

[Damon Heron]

Change "Allow zero length" in table design to Yes.

HTH
Damon

 

[Allen Browne]

If your data is entered through a form, you could use the BeforeUpdate event
of the form (not the control) to test whether the field IsNull() and if so
assign it a zls (zero-length string) instead.

The more fundamental question, though is why want to store a zls. Unless the
field is part of a multi-field index that requires unique values and cannot
ignore nulls, or you are actually trying to distinguish between the Null and
zls values, you cannot escape from nulls just by inserting a zls. As soon as
you use an outer join query, you are back to dealing with nulls again, so
ALL or your code and query critiera will have to test for both conditions.

If it's just that you are struggling with nulls, this article may help you
avoid the usual pitfalls:
Common errors with Null
at:
http://members.iinet.net.au/~allenbrowne/casu-12.html

 

[Guest]

Thanks for your reply, this is exactly what I ended up doing to solve the
problem. Should have checked my email last night I guess, I would have saved
myself some time.