When is a Null not a Null?

[Fluff]

This must be one of those VB quirk problems.

All I want to do is determine whether or not the 'value' part of a text box
is empty & react accordingly.
The code I have is this:

If txtMemberFirstname.Value = Null Then
response = MsgBox("You must enter a First Name for the new member",
vbOKOnly + vbExclamation, "Missing Data")
txtMemberFirstname.SetFocus
End If

In debug, if I look at the value of 'txtMemberFirstname.Value' it is
reported as Null, but the 'if' statement is stepped straight over.
I have tried Null, 0, and "" as the compared value but none of them get the
code to execute.

Please can anybody tell me why?

Many thanks,
Fluff
P.S. - I also tried 'len(txtMemberFirstname.value)', but that comes back as
Null as well

[Ken Zigler]

Fluff,

try the IsNull function:

If IsNull(txtMemberFirstname.Value) Then

response = MsgBox("You must enter a First Name for the new member",
vbOKOnly + vbExclamation, "Missing Data")
txtMemberFirstname.SetFocus
End If

Incidentally, you can omit the default property of a textbox control
(Value):

If IsNull(txtMemberFirstname) Then
... etc

Hope that helps.

For some good information about Nulls, see:

http://www.wa.apana.org.au/~abrowne/casu-11.html

http://www.wa.apana.org.au/~abrowne/casu-12.html

Zigler

Fluff <fl...@mad.scientist.com> wrote in message
news:3c012fe1$1...@mk-nntp-1.news.uk.worldonline.com...

[Bob Barrows]

On Sun, 25 Nov 2001 17:52:35 -0000, "Fluff" <fl...@mad.scientist.com>
wrote:

>This must be one of those VB quirk problems.
>
>All I want to do is determine whether or not the 'value' part of a text box
>is empty & react accordingly.
>The code I have is this:
>
>If txtMemberFirstname.Value = Null Then
> response = MsgBox("You must enter a First Name for the new member",
>vbOKOnly + vbExclamation, "Missing Data")
> txtMemberFirstname.SetFocus
>End If
>
>In debug, if I look at the value of 'txtMemberFirstname.Value' it is
>reported as Null, but the 'if' statement is stepped straight over.
>I have tried Null, 0, and "" as the compared value but none of them get the
>code to execute.
>
>Please can anybody tell me why?
>

Firstly, the comparison operator for Null is the word "Is", as in:

If SomeVariable Is Null Then

When you use "=" in conjunction with Null (or Nothing) it is
interpreted as an attempt to set the variable to Null or Nothing as
opposed to comparing the variable to Null or Nothing.

Alternatively, you can use the VBA IsNull funtion:

If IsNull(SomeVariable) Then

Unfortunately, this will not solve your problem. If the user types
something into the textbox, and then backspaces or deletes it, the
textbox will contain an empty string, not a Null. To get around this,
I usually use the Len function, which handles both cases:

If Len(somevariable) > 0 Then

This will work whether the textbox contains Null or "".

HTH,
Bob Barrows
Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often.

[Bruce M. Thompson]

Fluff:

Whereas "Null" is an unknown value, you cannot compare "Null" to "Null" (you
can't compare an unknown to an unknown). What you need to use is the "IsNull()"
function:

If IsNull(txtMemberFirstname.Value) Then

--
Bruce M. Thompson, Microsoft Access MVP
bthmpson@big~SPAM_NOT~foot.com
>>No Email, Please. Keep all communications
within the newsgroups so that all might benefit.<<

"Fluff" <fl...@mad.scientist.com> wrote in message
news:3c012fe1$1...@mk-nntp-1.news.uk.worldonline.com...

[Dimitri Furman]

On Nov 25 2001, 12:52 pm, "Fluff" <fl...@mad.scientist.com> wrote in
news:3c012fe1$1...@mk-nntp-1.news.uk.worldonline.com:

> All I want to do is determine whether or not the 'value' part of a text
> box is empty & react accordingly.
> The code I have is this:
>
> If txtMemberFirstname.Value = Null Then
>

You cannot use the '=' operator to test for Null. In VBA, you should use
the IsNull() function:

If IsNull(txtMemberFirstname.Value) Then

To determine if a text box is 'empty', it is more robust to use the
following, which also handles the case when the textbox value is an empty
string:

If Len(txtMemberFirstname.Value & vbNullString) = 0 Then

--
(remove a 9 to reply by email)

[Mike Reid]

Try the following:

If IsNull(txtMemberFirstname) = true then

On Sun, 25 Nov 2001 17:52:35 -0000, "Fluff" <fl...@mad.scientist.com>
wrote:

>This must be one of those VB quirk problems.

[Peter Russell]

The function you need is IsNull(myvalue).

Comparing something to Null, AFAIK, always gives False (unless you have assigned the value Null), because Null is an indeterminate value.
You can also look at IsEmpty - which does a similar thing for variants.

Regards

Peter Russell

[Bob Barrows]

On Sun, 25 Nov 2001 18:10:29 GMT, reb_...@yahoo.com (Bob Barrows)
wrote:

>>
>Firstly, the comparison operator for Null is the word "Is", as in:
>
>If SomeVariable Is Null Then
>

Oops - my bad. This syntax only works in queries, not in VBA. Trying
to use it in VBA results in an "Object Required" error.

[David W. Fenton]

fl...@mad.scientist.com (Fluff) wrote in <3c012fe1$1_1@mk-nntp
-1.news.uk.worldonline.com>:

>All I want to do is determine whether or not the 'value' part of a
>text box is empty & react accordingly.

Allen Browne has an excellent article on his website that discusses
various issues surrounding Null values:

Common Errors with Null
<http://wa.apana.org.au/~abrowne/casu-12.html>

and more basic:

Nulls: Do I need them?
<http://wa.apana.org.au/~abrowne/casu-11.html>

These are well-written explanations of the basic concepts and how
to deal with them.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Patrick Finucane]

Fluff wrote:

If you simply don't care, you can always use NZ() besides IsNull(). For
example:
If NZ([NumericField,0) > 0 Then
If NZ([TextField,"") > "" Then

[Murphy]

if IsNull(MyBox) or is "" then
dowhatever

This checks to make sure the box has something in it.. Im a "simple"
programer and as far as my database record keeping is concerned, a NULL or
ZeroLength String are the same to me.. The above code should make sure a
user enters something in the box.
(I fully understand that Nulls and ZeroLength strings are completely
different to VBA code but as I said, they may as well be the same when a
user is looking up info.)

Murphy

Fluff <fl...@mad.scientist.com> wrote in message
news:3c012fe1$1...@mk-nntp-1.news.uk.worldonline.com...

[Lyle Fairfield]

Were Godiva and The Emperor wearing the same thing?

[Wayne Gillespie]

On 25 Nov 2001 16:34:53 -0800, lyle...@yahoo.com (Lyle Fairfield)
wrote:

>Were Godiva and The Emperor wearing the same thing?

Does everything include nothing?

[Henry Craven]

You can't wear "Nothing".

--
Henry Craven
---------------
H_Cr...@bigpond.com

"Wayne Gillespie" <bes...@NObestfitsoftwareSPAM.com.au> wrote in message
news:f5830uofr1k75a1d2...@4ax.com...

[Larry Linson]

"Lyle Fairfield" wrote

> Were Godiva and The Emperor wearing the same thing?

<Chuckle>

But, isn't the burning question of our time, "Was Lady Godiva a red-head?"

[Allen Browne]

Wayne Gillespie wrote:
> Does everything include nothing?

Yes. But you could also say that everything excludes nothing.

What I'm unsure of is whether nothing can include nothing. ;-\

[Wayne Gillespie]

That would depend on whether nothing is classed as something.
I think nothing could include nothing but it certainly couldn't
include something.

Or could it?

[Lyle Fairfield]

Not exactly ... it's
"Is she a true red head?"

"Larry Linson" <larry....@ntpcug.org> wrote in message news:<31iM7.476$IQ1....@paloalto-snr1.gtei.net>...

> "Lyle Fairfield" wrote
>
>> Were Godiva and The Emperor wearing the same thing?

> But, isn't the burning question of our time, "Was Lady Godiva a red-head?"

[Brendan Reynolds]

Just to clarify - In SQL, use Is Null Whatever. In VBA, use
IsNull(Whatever).

--

Brendan Reynolds
bren...@indigo.ie

"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:3c014c25...@news.charter.net...

[Brendan Reynolds]

Not False, Peter, Null:

? 1 = Null
Null

If you thing of Null as representing an unknown value, this is quite
logical. Is 1 equal to an unknown value? The answer is unknown, in other
words, Null.

--

Brendan Reynolds
bren...@indigo.ie

"Peter Russell" <prus...@russellscott.co.uk> wrote in message
news:memo.20011125...@russellscott.compulink.co.uk...

[Peter Russell]

Yes , I agree. I was being sloppy.
In my head I was trying to say that it could not be TRUE ( which was the issue with the relevant piece of code) . I just didn't take sufficient care with what I
wrote.

Thanks

Peter Russell

[peter walker]

In Godiva's case that may be easier to answer than usual...but we won't go
there <g>
"Lyle Fairfield" <lyle...@yahoo.com> wrote in message
news:42d36dfd.0111...@posting.google.com...

[Michael Blake]

Maybe nothing doesn't exist and it is just something that we can't observe.
(c;

Michael

"Allen Browne" <abr...@odyssey.apana.org.au> wrote in message
news:3C01E5A6...@odyssey.apana.org.au...

[David W. Fenton]

Gospodyn...@nyet.net (Henry Craven) wrote in
<0lhM7.372953$bY5.1...@news-server.bigpond.net.au>:

>You can't wear "Nothing".

Ah yes, but since I've been known to exclaim "What is *she*
wearing?", it's quite clear that one can wear a Null (i.e.,
undefined).

I hear it's in for Spring.

[Larry Linson]

"Henry Craven" wrote

> You can't wear "Nothing".

Per your statement, "One 'cannot wear Nothing'." But it is also clear that
"One 'can not wear Anything'."

So where does that leave us, other than waxing Phil O'Sophical?

And, does Ol' Phil really want to be waxed?

[Fluff]

Erm.... Any chance of a helpful reply. Hmm?

[Marshall Barton]

Well, they're all helpful in their own way. Hidden amongst
all Phil O'Sophical's stuff, Brendan's first response is
short, to the point and directly answers your syntax
question.

If you need that translated to you exact If statement, try
this:

If IsNull(txtMemberFirstname) Then

Marsh

Fluff wrote in message
<3c0414c1$1...@mk-nntp-1.news.uk.worldonline.com>...

[Fluff]

Thanks marshall.
Something weird's going on though.....
I haven't got a response from anyone called Brendan in my message list !
I can only see replies from David, Larry, and yourself.

Regards
Fluff

"Marshall Barton" <marsh...@Mindspring.com> wrote in message
news:9u15gm$v3j$1...@slb5.atl.mindspring.net...

[Marshall Barton]

Bummer of a news server you've got.

From my view of the thread at this time, there are 27
responses from 16 different people. Most of it was kind off
the wall, but if you want to see what you started, search
google for your post.

http://groups.google.com/groups?hl=en&safe=off&group=comp.da
tabases.ms-access

Marsh

Fluff wrote in message
<3c043fd8$1...@mk-nntp-1.news.uk.worldonline.com>...

[Henry Craven]

While "Anything" is a Superset and it is true that you cannot
wear "everything" in "anything" such as Racoons, if you are
Davey Crocket you can wear certain "Somethings" being
subsets of "Anything" Vis A Vis Racoon Skin Caps.

...and as to Phil -n- Sophie, if they don't like waxing then
they must be waning, [ liberal couple seeks 3rd for mutual
mooning reply NY 123] . and how would Wayne feel about that ?

:-)

--
Henry Craven
---------------
H_Cr...@bigpond.com

"Larry Linson" <larry....@ntpcug.org> wrote in message

news:iqPM7.272$Wm1.1...@paloalto-snr1.gtei.net...

[Henry Craven]

The answer being of course that she was wearing "Next To Nothing",
which again begs the question, was it "Next, to Nothing", or was it
"Next to, Nothing" and if so what was/is that, and is it undefinable.
( it usually is, for the parents..)

{according to Peeping Tom Lady G was a natural Redhead
.....but dyed her hair blond.. ;-) }

--
Henry Craven
---------------
H_Cr...@bigpond.com

"David W. Fenton" <dXXXf...@bway.net> wrote in message
news:9165E7797df...@news-server.nyc.rr.com...

[Michael Blake]

Have you downloaded all of the headers? It looks like you are using Outlook
Express, the default number of headers it downloads at a time is 100. You
may need to click on the 'Headers' button a couple times to see all of the
messages. The other possibility is that your news server does not keep very
many messages.

As far as your question, I believe it was Gary Rockley that posted this
solution:

If txtMemberFirstName & "" = "" Then
Your Code Here...
End If

HTH
Michael

"Fluff" <fl...@mad.scientist.com> wrote in message

news:3c043fd8$1...@mk-nntp-1.news.uk.worldonline.com...

[Allen Browne]

Sub TestNothing()

Dim Everything As Object

If Everything Is Nothing Then
MsgBox "This is a paradox of the Object-Oriented universe."
End If

End Sub

:)

--
Allen Browne (Microsoft Access MVP)
Perth, Western Australia
Tips for MS Access users at:
http://odyssey.apana.org.au/~abrowne

[Wayne Gillespie]

On Thu, 29 Nov 2001 14:03:57 +0800, Allen Browne
<abr...@odyssey.apana.org.au> wrote:

8-)