Question Comparing strings

[NotSoRandomOne]

I really want to just compare two strings and see if they are the same. Of course, the first thought is to write

if str1 <> str2 then doStuff

But that won't work, because Access doesn't work that way. So I tried this:

    Dim temp As Variant
    temp = StrComp(freqBoxValue, FrequencyScheduleBox.Value)
    If temp <> 0 Or temp = Null Then
        doStuff
        End If

when freqBoxValue = Null, and StrComp returns Null, I thought this should 'doStuff'. It doesn't. What am I missing?

Background: I'm trying to see if a text box value on a form has changed from its initial value during OnUpdate. If it has changed, doStuff.

 

[wazz]

you can compare with OldValue.

 

[boblarson]

And just a reminder that you do not check to see if anything is EQUAL to NULL.

You use IsNull(whatever) or Whatever Is Null but never Whatever = Null

 

[NotSoRandomOne]

...You use IsNull(whatever) or Whatever Is Null but never Whatever = Null

That's what I was missing. I wonder how many times that will bite me in the future.

Thanks to both of you.

 

[gemma-the-husky]

incidentally, you can do

if str1<> str2

however, if one might be a null then you are better doing

if nz(str1,vbnullstring) <> nz(str2,vbnullstring)


note also that out of the box, this comparison is case insensitive


so "myname"="MYNAME" is true.

 

[boblarson]

and just as a side note about vbNullString (because the name is a misnomer) -

vbNullString is actually the same as an empty string "" not null. It is easy to get confused as a beginner so probably a good thing to mention here.

 

[NotSoRandomOne]

Thank you for the additional information. It helps make the code prettier, and that is always a worthy endeavor! I take it that "nz" comes from "Not Zero"?

David

 

[Atomic Shrimp]

Nz comes from 'null to zero' - meaning it converts on the fly.

 

[NotSoRandomOne]

Thanks!

David

 

[Galaxiom]

Nz comes from 'null to zero' - meaning it converts on the fly.

I have often wondered about the derivation.

Although Nz() is often used to set nulls to zero this is only a subset of its capability since the null substitution can be defined at will and is not necessarily zero. Not such a good name really.

I prefer to think of it as the Nullz function which is easier than "N-Z" or "Nulls to Zero". It is rederived from the representation of Z being the final character of the null. The term doesn't clash with Null itself.

I had to come up with a name or it was going to become the kiwi function since NZ is New Zealand for those of the Antipodes.

 

[Atomic Shrimp]

The 'zero' bit refers to its default behaviour - if no substitute value is specified, it returns a zero or zero-length string. (well that's what the help says, I have a feeling that it doesn't automatically return a numeric zero for a numeric null input)

I wasn't aware of the optional ValiueIfNull parameter until comparatively recently... is it the case that earlier versions of VBA didn't have it?

 

[boblarson]

NZ with the ability to set the default for null has been around for quite a long time. However, in some real old versions it didn't even exist. There was a function called NullToZero and it ONLY was able to set nulls to zero.