when is null field not null?

  • Thread starter Thread starter JethroUK©
  • Start date Start date
J

JethroUK©

i have two fields (combos as text) that are reporting not null -
ISNULL(myfield) = false - when in fact they clearly are - the other 30 or so
fields are reporting correctly using the same ISNULL()

any clues ?
 
If the myfield is a text type field, you need to test for empty also

If Not ISNULL(myfield) And myfield <> "" Then

Or to make it shorter
If Len(myfield & "") > 0 Then
 
i've tried Len(myfield & "") > 0 & ISEMPTY - but neither can detect these
two null fields - i'll have to look at something else - thanks
 
Ofer Cohen said:
If the myfield is a text type field, you need to test for empty also

If Not ISNULL(myfield) And myfield <> "" Then

Or to make it shorter
If Len(myfield & "") > 0 Then
Click to expand...

Or else use an update query to update the values "" in the table to make
them Null, then change the field so that it doesn't allow zero-length
strings, and change any code that assigns "" to the field.
 
Is Empty is used for something altogether different and
totally inappropriate here. The use of & in this context is
also wrong.

If you are not going to fix things as Dirk said, then at
least try using what Ofer suggested - after changing myfield
to the name of the field to what you are actually using.
 
you're quite right - Well spotted - you weren't to know, but the combos
involved were actually lookups, and whilst the visible column was null, the
underlying numeric column (invisible) was defaulting to zero - your
principle still applies - i now have this default to null and have run an
update query to bring the existing records in line
 
yes - i found out ISEMPTY is inappropriate - alas Dirk did spot the root of
the problem and i've fixed it as he describes


Marshall Barton said:
Is Empty is used for something altogether different and
totally inappropriate here. The use of & in this context is
also wrong.

If you are not going to fix things as Dirk said, then at
least try using what Ofer suggested - after changing myfield
to the name of the field to what you are actually using.
--
Marsh
MVP [MS Access]


JethroUK© said:
i've tried Len(myfield & "") > 0 & ISEMPTY - but neither can detect these
two null fields - i'll have to look at something else - thanks



30
or so
Click to expand...
Click to expand...
 
Jethro,

That should be:
Len(myfield + "") > 0

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
 
Graham R Seach said:
Jethro,

That should be:
Len(myfield + "") > 0
Click to expand...

Why do you say that, Graham? If [myfield] is Null, then ([myfield] +
"") will be Null, and Len([myfield] + "") will be Null. I usually code
a test for "Null or a zero-length sting" as Len([myfield] & ""), just as
Jethro already had it.
 
Dirk,

Yes, I apologize. You're correct, as are you Jethro. For some reason, I was
thinking about propagating nulls in queries. In fact, I use Jethro's syntax
in VBA quite a lot. I'm afraid there's a lot going on right now, and
obviously my brain isn't working at all.

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia


Dirk Goldgar said:
Graham R Seach said:
Jethro,

That should be:
Len(myfield + "") > 0
Click to expand...

Why do you say that, Graham? If [myfield] is Null, then ([myfield] +
"") will be Null, and Len([myfield] + "") will be Null. I usually code
a test for "Null or a zero-length sting" as Len([myfield] & ""), just as
Jethro already had it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top