I downloaded a Database Issue Checker from AllenBrowne.com and one of the things it checks for is if you have allowed zero lenth in any of your tables. I am assuming (which isn't a good thing to do) that having Allow Zero Length set to yes has more bad effects than good ones. When I set the Allow Zero Length to No on the table I store my information about my forms (documenting my database), something goes wrong and some of the controls on my form are passed over by the code. I decided not to turn them all off at once and turned just one off so that I could figure out how to make it work for one and then fix the rest of it. Here is the one I tried first:



Not sure how to check for zero length and change it (if that is what I need to do). I have tried the NZ function to make it equal a space if it was null but without luck.

Any suggestions would be appreciated!
Lena

Lena, the thing is that even if you have a field that's set to be required & disallow ZLS, there's still a chance that the control that's bound to the field can return a null value. Therefore, regardless of field's attributes, I'd always check for Nulls/ZLSs. I typically do this:



I'm not sure what your code snippet is trying to do, though? Why are you looking at ControlSource?

Hard to tell from that little snippet of code.

Allow zero-length string (ZLS) means that text and memo fields will store "" (note there is nothing between the quotes) or the fields can store null.

A ZLS can be assigned to a string variable, a null cannot be assigned to a string variable. So assuming that strControlSource dimensioned as a string, you cannot assign a null value to it. You will get a type mismatch error.

In VBA, you can check for null values using the IsNull function - IsNull([SomeControl]). In a query you use the Is Null comparison - [FieldName] is Null - or you can use the slower VBA function - IsNull([FieldName]).

You check for a ZLS with X = ""

If you don't care if the value is null or a ZLS or a string of spaces, you can use
Len(Trim(X & ""))=0
That expression will return true is X is null, a ZLS, or if X is " ".

I am documenting my database by storing some of the properties of my forms in a table and then I print them out on a report. Similar to what the Analyzer does but laid out in a format I understand better.

I will give your example at try and see what happens.

Lena

IF the field is set to not allow Zero-length strings, you either need to set its value to Null or not set it at all.

If Len(X & "") = 0 THEN
FieldOrControl = Null
Else
FieldOrControl = X
End IF