prevent zero length entries

I have a couple critical fields where I am setting them to no nulls but just
realized that they can be set to zero length strings very easily. Other than
a trigger or handling the validation in the front end, is there an easy way
to handle this?
MS Access is my front end so interestingly enough, if you select an the
entire text in a field and hit the delete key it trys to set to NULL not ""
so it's ok. But in EM you can just hit the delete key and it goes to ""
instead of NULL. Obviously end users are goign to use the front end and not
EM so this isn't really a problem but I was just wondering about other front
ends that might not behave the same way. They might set to "" on hitting the
delete key.
THanks,
KeithKeith G Hicks wrote:
> I have a couple critical fields where I am setting them to no nulls but ju
st
> realized that they can be set to zero length strings very easily. Other th
an
> a trigger or handling the validation in the front end, is there an easy wa
y
> to handle this?
> MS Access is my front end so interestingly enough, if you select an the
> entire text in a field and hit the delete key it trys to set to NULL not "
"
> so it's ok. But in EM you can just hit the delete key and it goes to ""
> instead of NULL. Obviously end users are goign to use the front end and no
t
> EM so this isn't really a problem but I was just wondering about other fro
nt
> ends that might not behave the same way. They might set to "" on hitting t
he
> delete key.
> THanks,
> Keith
>
A check constraint of LEN(ColumnName) > 0 would do it, or you could work
in a more complex requirement. Honestly, if you're worried about people
just putting in an empty string, and you require at least one char,
they'll learn that if they put in 1 char they can save the data. If you
bump the requirement up to 2, they'll put in 2. :D
Zach|||keith,
create a check constraint on table, with expression: datalength(col)>0
hth,
dean
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23w4ZvbwCFHA.1932@.TK2MSFTNGP14.phx.gbl...
> I have a couple critical fields where I am setting them to no nulls but
just
> realized that they can be set to zero length strings very easily. Other
than
> a trigger or handling the validation in the front end, is there an easy
way
> to handle this?
> MS Access is my front end so interestingly enough, if you select an the
> entire text in a field and hit the delete key it trys to set to NULL not
""
> so it's ok. But in EM you can just hit the delete key and it goes to ""
> instead of NULL. Obviously end users are goign to use the front end and
not
> EM so this isn't really a problem but I was just wondering about other
front
> ends that might not behave the same way. They might set to "" on hitting
the
> delete key.
> THanks,
> Keith
>|||Thank you to both of you. That works just fine of course.
I feel sort of silly now. :)|||"Zach Wells" wrote:
<snip>

> Honestly, if you're worried about people just putting in
> an empty string, and you require at least one char, they'll
> learn that if they put in 1 char they can save the data. If
> you bump the requirement up to 2, they'll put in 2. :D
LOL! Never underestimate the ability of users to circumvent your data
validation routines. We had a situation just like that. You could even map
out the evolution of the validation routine and the users' innovation
(especially since the application had no way of exactly determining what a
correct string was for that particular column). The progression looked
something like:
.
NA
N/A
XXXX
DONTKNOW
:)
Craig