Converting Nulls Form Entries to Zero-Length Strings

[tylerg11]

My database is setup to not except nulls. So, on my forms, if I leave a field blank, I get an error about the Null entry. If I enter a "" in the field and tab out of it, it appears blank, but is a zero-length string entry. How can I automatically take all Null form field entries and convert them to zero-length string values before the save happens so that I don't get this error? Thanks.

[RuralGuy]

Have you looked at the Nz() function?

[alansidman]

In your table properties, set the default value to " ".

[tylerg11]

In your table properties, set the default value to " ".

I have. But if the field already contains data, and the user removes it, then it will error.

[tylerg11]

Hane you looked at the Nz() function?

Yes. I'm not sure completely on how I would implement it. Would it occur on data entry?

[RuralGuy]

I have. But if the field already contains data, and the user removes it, then it will error.

The default property *only* applies if there is no current value.

[alansidman]

In the afterupdate event for the field, try this:

if me.yourfieldname(isNull) then
me.yourfieldname.value = " "
End if

[RuralGuy]

@Alan:
1) You may want to make that If IsNull(Me.yourfieldname) Then
2) You only get an AfterUpdate event *if* the user changed the control - perhaps the Exit event
3) You can set a field to "" (ZLS) rather than " " (a single space)

[alansidman]

@Allan
Thanks for the code correction. I was just thinking about it and came back to change it when I saw your post. I'm a little dislexic today.
Good Point on the AfterUpdate. I like the Exit Event better.


Alan