CODEWITHSUNDEEP
vbafunctionms-access
bdb325
bdb325

Reputation: 9

VBA for Access- Compare function reporting "false" or #error everytime

I am developing an Access DB for a little project at work. We have an ODBC Database that has a list of certain servers entered into a web client. I tried writing a VBA function to take them, convert them to lowercase, and assign the string to "true" if they match, and "false" if not. I use the function in a query so you can search for ranges of server and so-on and so-forth. 

Public Function InWhatsUp(field1 As Field, field2 As Field) As String
  If (LCase(field1.Value) = LCase(field2.Value)) Then
    InWhatsUp = "True"
  Else
    InWhatsUp = "False"
  End If
 Return
End Function

I haven't been able to find much relevant research besides using an IIF to compare, but the results all turn up as "false" there as well. Thank you in advance

Upvotes: 1

Views: 334

Answers (3)

Vityata
Vityata

Reputation: 43595

Public Function InWhatsUp(field1 As Variant, field2 As Variant) As Boolean

    If IsNull(field1) Or IsNull(field2) Then Exit Function
    If (Len(field1) = False) Or (Len(field2) = False) Then Exit Function
    If IsError(field1) Or IsError(field2) Then Exit Function

    InWhatsUp = StrComp(field1, field2, vbTextCompare)

End Function

Public Sub TestMe()
    Debug.Print InWhatsUp("aaB", "aAb")
    Debug.Print InWhatsUp("aaB", "abb")
    Debug.Print InWhatsUp(Null, "")
End Sub
  • You do not have return in . Instead you refer to the function.
  • It is a good idea to make the function Boolean, if the only two values to return are True and False.
  • As far as you are working in Access, probably there could be one exception - if the two strings are "", probably you would like to see False, depending on your business logic. The IsError() is due to the same logic.
  • Checking whether the input is Null is something that is quite handy in Access. And following the logic of plenty of languages, a Null is never the same as another Null.

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71207

I'd make that function return a Boolean. Its job is to determine a Boolean result - whoever calls it can then decide to turn that Boolean into a String, but most callers would normally run Boolean logic with it, and if that's the case then returning a String requires VBA to perform implicit type conversions all over the place.

And since it's a string utility function, it doesn't even need to know about a Field - give it two Variant values so it can take a Null, and pass them by value:

Public Function AreTheSame(ByVal string1 As Variant, ByVal string2 As Variant) As Boolean
    If IsNull(string1) Or IsNull(string2) Then Exit Function
    AreTheSame = (StrComp(string1, string2, vbTextCompare) = 0)
End Function

Now you can test this function anywhere, with any input:

?AreTheSame(Null, "ABC")  -> False
?AreTheSame(Null, Null)   -> False
?AreTheSame("abc", "ABC") -> True

If you want to treat to Null values as equal, then you need to coalesce them into empty strings - and since null-coalescing is a concern of its own, I'd make another, separate function for that:

Public Function Coalesce(ByVal value As Variant) As String
    If IsNull(value) Then
        Coalesce = vbNullString
    Else
        Coalesce = CStr(value)
    End If
End Function

Public Function AreTheSame(ByVal string1 As Variant, ByVal string2 As Variant) As Boolean
    AreTheSame = (StrComp(Coalesce(string1), Coalesce(string2), vbTextCompare) = 0)
End Function

With that, two null values are treated as empty strings. I'd probably use an optional parameter to make that behavior configurable from the call site - ditto for case-sensitivity.

Upvotes: 1

Andre
Andre

Reputation: 27644

A query can never pass objects like Field to a VBA function.

Use parameter data type String, or if the values can be NULL, Variant.

Since normal string comparison is always usually (see below) case-insensitive, you can then simply do

InWhatsUp = (str1 = str2)

or directly in the query:

Identical: IIf(server1 = server2, "True", "False")

If you need case-sensitive string comparison, you must use

StrComp(string1, string2, vbBinaryCompare)

Edit

normal string comparison is always case-insensitive

That's only true if you keep the Option Compare Database that is by default inserted in Access VBA modules.

If you change it to Option Compare Binary or remove it altogether, string comparisons are case-sensitive.

Thanks @ThunderFrame for the correction.

Upvotes: 1

Related Questions