If statement with a Null

[Ann]

I don't understand why the following doesn't work??

Private Sub txtDeductibleCreditsStartDate_Exit(ByVal Cancel As
MSForms.ReturnBoolean)

If optMMOGroupYes.Value = True And optDeductibleCreditsYes.Value = True And _
txtDeductibleCreditsStartDate.Value = Null Then
MsgBox "The Deductible Credits Start and" _
& vbCrLf & "End Dates must be entered."
Cancel = True

ElseIf optMMOGroupYes.Value = True And optDeductibleCreditsYes.Value =
True And _
txtDeductibleCreditsStartDate.Value <> Null Then
Cancel = False

End If

End Sub

I only had the first "and" in my statement. The message came up but of
course that kept me in the same field so I had to add another and
txtDeductibleCreditsStartDate.value = Null...or something. I thought this
would work. I'm just learning this so I don't know what to do. How else can
I say a field has nothing in it? Thanks for the help.

 

[Ann]

Duh, I just realized it's "".

Thanks though!

 

[Cindy M.]

Hi Ann,

I don't understand why the following doesn't work??

ElseIf optMMOGroupYes.Value = True And optDeductibleCreditsYes.Value =
True And _
txtDeductibleCreditsStartDate.Value <> Null Then

I think only Access uses a "Null" value for (database) fields.

If this is a text box control on a Word VBA UserForm (the topic of this
newsgroup), then the comparison would be:
Len(txtDeductibleCreditsStartDate.Value) > 0

If you are using Access, you need to ask in an Access newsgroup

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Ann]

I'm in the correct group. I am creating a User Form. I copied code from
http://www.fontstuff.com/vba/vbatut09pfv.htm and the use of Null was
repeatedly used. I don't know why it didn't work and if
Len(txtDeductibleCreditsStartDate.Value) > 0 is correct then I don't know
why using "" (empty quotation marks) did work, but they did. It makes it
extremely difficult when each application does it differently. I do
appreciate your help though and will try the
Len(txtDeductibleCreditsStartDate.Value) > 0 code if that is what is correct.
Thanks.

 

[Gordon Bentley-Mix]

Ann,

You can use either

Len(txtDeductibleCreditsStartDate.Value) > 0

or

txtDeductibleCreditsStartDate.Value <> ""

The result is the same. In the first instance, you are checking to see if
the length (Len) of the value is greater than zero - which it would be if it
wasn't blank. In the second instance, you are checking to see if the value is
not equal to nothing (""). Which one to use is more a matter of personal
preference - although I prefer the latter because it's a direct evaluation
rather than a 'functional' one.

However, in the structure of your code you would proably want to use

Len(txtDeductibleCreditsStartDate.Value) = 0

or

txtDeductibleCreditsStartDate.Value = ""

You can also simplify your code as follows:

Private Sub txtDeductibleCreditsStartDate_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
If optMMOGroupYes.Value = True And optDeductibleCreditsYes.Value = True
Then
If txtDeductibleCreditsStartDate.Value = "" Then
MsgBox "The Deductible Credits Start and" _
& vbCrLf & "End Dates must be entered."
Cancel = True
End If
End If
End Sub

A bit of explanation on the above:
* The nested 'If' statements are a bit more efficient in that you only need
to check the value of the txtDeductibleCreditsStartDate TextBox if both of
the option buttons have been selected.
* You only need one 'If' statement to check the value of the TextBox
because there are only two possibilities: either it's blank or it's not. The
'ElseIf' statement is redundant.
* The default value of the Cancel argument is 'False', so you can get away
with setting it to 'True' only if you need to - which you would do only if
the value of the TextBox is blank. (However, I often set it explicitly as the
first line of the procedure just as a reminder: Cancel = False. And in some
more complex instances I have been known to use a Function that returns a
Boolean value to set the Cancel argument - usually when the TextBox is in a
Frame and I want to validate the TextBox on the Frame Exit event as well...
EEK!)

Finally, I don't know why that website would 'Null' in Word VBA examples;
I've never seen it, and a search of the VBA help only returned 3 topics -
none of which would be applicable to your situation...
--
Cheers!
Gordon
The Kiwi Koder

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Cindy M.]

Hi Gordon,

Which one to use is more a matter of personal
preference - although I prefer the latter because it's a direct evaluation
rather than a 'functional' one.

Len() is faster (although on a small scale you'd never notice),
which is why it is "preferred".

you are checking to see if the value is
not equal to nothing ("")

Technically, not Nothing (which means an uninstantiated object,
basically the Null Ann was trying to use). "" is an zero-length
(or empty) string.

I don't know why that website would 'Null' in Word VBA examples;

I skimmed through it very quickly, and only saw it used as a way
to reset (void) all the control's values in the form. Technically
incorrect, of course, but it appears to work in that context. VBA
can be very forgiving, at times - looks like it's automatically
converting "Null" to whichever "empty" value the particular
control expects.

Cindy Meister

 

[Jonathan West]

I skimmed through it very quickly, and only saw it used as a way
to reset (void) all the control's values in the form. Technically
incorrect, of course, but it appears to work in that context. VBA
can be very forgiving, at times - looks like it's automatically
converting "Null" to whichever "empty" value the particular
control expects.

Null is also a keyword to indicate that a Variant variable contains no valid
data

 

[Ann]

Thank you for the help Gordon. I'm just learning so it's great to have the
help and explanations. I understand what you wrote and why it is right to do
it this way. I'm going to replace what I wrote with yours.

Thanks again!

 

[Jonathan West]

Ann,

You can use either

Len(txtDeductibleCreditsStartDate.Value) > 0

or

txtDeductibleCreditsStartDate.Value <> ""

The result is the same. In the first instance, you are checking to see if
the length (Len) of the value is greater than zero - which it would be if
it
wasn't blank. In the second instance, you are checking to see if the value
is
not equal to nothing (""). Which one to use is more a matter of personal
preference - although I prefer the latter because it's a direct evaluation
rather than a 'functional' one.

Compared to the length of time spent executing any command which accesses
the Word object model, the difference in speed is trivial, but in fact the
Len method is faster.

In order to understand why, it is necessary to consider how strings are
stored in VB & VBA. Unlike in C/C++, where is string is a sequence of bytes
terminated with a null character, a VBA HLSTR structure consists of a 4-byte
value which specifies the length of the string, followed by the string's
contents. (For those of you who have tried using the Windows API functions
and been baffled by the convolutions you have to go through in order to get
strings out of API functions, this diifference in structure is the reason
why)

To get the length of a VB string, all that need be done is to read the value
of the 4-byte header. To compare the string with another string, both
strings must be pushed onto the stack, then their headers compared, and
then (if they are equal) a character-by-character comparison made. it is
obvious which will be the quicker process.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

 

[Tony Jollans]

How would one use it for that purpose?

 

[Jonathan West]

How would one use it for that purpose?

the following code snippet illustrates the use of Null, and how to
distinguish between a variant being null and merely being empty or
uninitialized, and the effect on the Len function of having a null value in
a variant.

Dim x As Variant
Debug.Print IsNull(x), IsEmpty(x), Len(x)
x = "sss"
Debug.Print IsNull(x), IsEmpty(x), Len(x)
x = ""
Debug.Print IsNull(x), IsEmpty(x), Len(x)
x = Null
Debug.Print IsNull(x), IsEmpty(x), Len(x)
x = Empty
Debug.Print IsNull(x), IsEmpty(x), Len(x)

 

[Tony Jollans]

All that that shows is that the IsNull function (correctly) returns True for
a variable which has been explicitly set to Null (and returns False at all
other times). It works with a Variant because only a Variant can be set to
Null.

The result of doing most anything with a Null is, itself, Null. There are a
few exceptions (concatenation springs to mind) where a null can,
effectively, be coerced to an empty string but the Len function is not one
of them.

Although I would be happy to be shown wrong, I do not believe Nulls occur (I
do realise that's an oxymoron <g>) naturally anywhere in Word, and only
occur occasionally in Excel (the intersection of two disparate ranges, for
example); they are primarily a relational database concept.

 

[Jonathan West]

All that that shows is that the IsNull function (correctly) returns True
for a variable which has been explicitly set to Null (and returns False at
all other times). It works with a Variant because only a Variant can be
set to Null.

The result of doing most anything with a Null is, itself, Null. There are
a few exceptions (concatenation springs to mind) where a null can,
effectively, be coerced to an empty string but the Len function is not one
of them.

Although I would be happy to be shown wrong, I do not believe Nulls occur
(I do realise that's an oxymoron <g>) naturally anywhere in Word, and only
occur occasionally in Excel (the intersection of two disparate ranges, for
example); they are primarily a relational database concept.

Ah, that's a slightly different question from what you asked. You are now
asking essentially "*why* would you use it?" whereas before you asked "how
would you use it?

And I would have to admit that I can't remember ever having found a use for
Null in my Word VBA programming.

It is there because VBA is essentially the same language as (Classic) VB,
but with the Word object model tacked on. VB was often used as a database
front end.

 

[Tony Jollans]

Ah, that's a slightly different question from what you asked. You are now
asking essentially "*why* would you use it?" whereas before you asked "how
would you use it?

And I would have to admit that I can't remember ever having found a use
for Null in my Word VBA programming.

It is there because VBA is essentially the same language as (Classic) VB,
but with the Word object model tacked on. VB was often used as a database
front end.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

Fair point!

Nulls are so often misunderstood (even by database programmers). Of course
the term has (at least) two meanings, and nonsense such as IsNull(vbNull)
returning False doesn't help.

 

[Karl E. Peterson]

Although I would be happy to be shown wrong, I do not believe Nulls occur (I
do realise that's an oxymoron <g>) naturally anywhere in Word, and only
occur occasionally in Excel (the intersection of two disparate ranges, for
example); they are primarily a relational database concept.

Yeah, if you're accessing a database from within word, to do a mailmerge for
example, they can and do come up then. That's definitely the overwhelmingly typical
case.

 

[Gordon Bentley-Mix]

Cindy, et al:

Thanks for the _very_ thorough explanation. I certainly have a better
understanding of both the Len function and Null. I also appreciate the
reasoning behind using Len over an empty string comparison. The empty string
comparison seems more intuitively obvious, but since Len runs faster, I'll
endeavour to use it from now on. (And I do recognise the difference between
'nothing' and 'Nothing' - just trying to keep things simply for Ann.)

An interesting use of Null in the web article. The VBA help says that Null
only applies to controls that have a .TripleState property: CheckBox,
OptionButton and ToggleButton. Obviously it works with other controls as
well, but I don't plan on using it any time soon...

Thanks again to everyone who contributed to this thread. Ask a simple
question and WOW!
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Cindy M.]

Hi gordon,

An interesting use of Null in the web article. The VBA help says that Null
only applies to controls that have a .TripleState property: CheckBox,
OptionButton and ToggleButton. Obviously it works with other controls as
well, but I don't plan on using it any time soon...

Right, I'd forgotten about that. Thanks for the reminder

-- Cindy Meister