StrComp causing "Invalid use of Null" error

[sephiroth2906]

Hello folks! I am trying to learn VBA and found a book to help me. I am using some code it gave me for an example of StrComp:

Private Sub cmdCompare_Click()
Dim iResult As Integer

iResult = StrComp(txtFirst.Value, txtSecond.Value)

Select Case iResult

Case -1
MsgBox "The first string is less than the second"

Case 0
MsgBox "Both strings are equal"

Case 1
MsgBox "The first string is greater than the second"

Case Else
MsgBox "One or both of the strings are Null"
End Select

End Sub

The debugger states that the line:

iResult = StrComp(txtFirst.Value, txtSecond.Value)



is the culprit, each time I leave one of the text boxes empty. The companion software with the same code that I downloaded for the book does the same thing.

The book is for access 2007 and I have 2010, so I do not know if something changed or if it is just bad code, but I would like to know what is generating the error.

I appreciate the help!

[RuralGuy]

Change your code to:
iResult = StrComp(Nz(Me.txtFirst,""), Nz(Me.txtSecond,""))
...and see what happens.

[sephiroth2906]

It ignores the last Case. If one box is empty, it states the other is greater, and they are equal if they are both empty.

Is it possible that the code just will not work as it is written (well, I know it doesn't...)

Ok, I guess I want to know how to make it do what it was intended to, which is tell if one or both strings is null.

Oh, and if you can explain what I just put in there, (the Nz) it would be much appreciated!

Thanks!

[RuralGuy]

Okay, I see the problem. StrComp() returns a variant and you have it defined (Dim'd) as an Integer which will blow up when the Null is returned. Just change this line
Dim iResult As Integer
...to...
Dim iResult As Variant
...and the original code should work.
You can also look up the Nz() function in help to see that it is doing.

[sephiroth2906]

Ok, after looking up what a variant does that makes sense. If I have code that may return a Null value, then I have to use that instead. I appreciate the help!

[RuralGuy]

Glad we could help.