Invalid use of Null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a form with a comments and a history field. After the user types in
the new comments, they click a button to update the history field. This is
supposed to clear the comments field after updating history, but I keep
getting the error "Invalid use of null". Can you tell me what I am doing
wrong?

Arlene

Here is my code:

If Me.Comments <> "" Then
strComments = "" & Me.Comments
strOldComments = "" & Me.Comments
strHistory = Me.History
strOldHistory = Me.History
strNewHistory = Date & " - " & strComments & vbNewLine & strHistory
Me.Comments = ""
Me.History = strNewHistory
End If
 
A String variable cannot be Null.
The only VBA type that can be Null is Variant.

To achieve what you want, you probably need to test for Null rather than a
zero-length string, i.e.:
If IsNull(Me.Comments) Then

For more information, see:
Common errors with Null
at:
http://members.iinet.net.au/~allenbrowne/casu-12.html
The article explains 6 of the most common issues.
 
If the variables beginning with 'str' are String variables, then I would
expect this error if 'History' contains a Null value, as only Variant
variables can accept a Null value. Options include testing History for a
Null value before attempting to assign it, using NZ() or concatenation
(Me.History & "") to convert Null values to strings, or changing the String
variables to Variants.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Hi,

I don't think you should disregard the advice given by Allen and Brendan. If
you are still using that code you posted it is going to continue to fail,
even with the change you said you made. As the two MVP's that answered your
post pointed out, you can't assign a null to a string variable.

Your code starts out by testing for an empty string only:
If Me.Comments <> "" Then...
Click to expand...

If the value of Comments is null, your If test will still be True and the
next line will be executed:
strComments = "" & Me.Comments
Click to expand...

An error will occur on this line (for the reasons pointed out by Allen and
Brendan).

At the very least, change you If statement to:

If Me.Comments & "" = "" Then...

That way you know that the control will evaluate to an empty string (even if
it is null) and your string variables will work for you. Note that although
the test I have typed here evaluates to an empty string, the actual control
value is still null until you explicitly make it otherwise.

Jamie
 
Mmmm, humble pie...delicious.

I see what you have done now:
strComments = "" & Me.Comments
Click to expand...

So even if Comments is Null the variable will be an empty string. Should've
looked properly the first time shouldn't I?

Anyway, apologies.

Jamie
 
Back
Top