Checking Fields (1 Viewer)

[kitty77]

I have a form that check for a condition on several fields. I'm using the following...

If [field1] <> "*H*" then … (this works)
if [field1] <> "*H*" And [field2] <> "*H*" then... (does not work when I put two together)

I want to be able to check four fields at once. Not sure how to add them together.

Hope this makes sense... Thanks.

 

[jdraw]

Are you certain that this works?

If [field1] <> "*H*" then … (this works)

The usual set up for that construct would use the Like operator.
eg. if field1 not like "*H*".....

 

[kitty77]

yes, it does. Ok, then how would I check for multiple fields at once. Basically, I need to check if all fields do not have an "H"

 

[jdraw]

We really need more context for your question to know details of the requirement.
But to provide an answer, you could do something like:

....
Dim MyString as string
MyString = field1 & field2 & field3 & field4 ' concatenate to 1 string
if Instr(Mystring,"H") >0 then
  msgbox "At least 1 field has an 'H' "
else
  msgbox "No field has an 'H' "
end if
...

Good luck.

 

[The_Doc_Man]

if [field1] <> "*H*" And [field2] <> "*H*" then... (does not work when I put two together)

Syntactically, IF Field1 <> "*H*" AND Field2 <> "*H*" ... is valid for joining together a sequence of comparisons. You say it doesn't work. So... what is the behavior that identifies that it does not work? More specifically, does it throw an error message? Or is it that it just never takes the THEN branch of the IF statement? Also, jdraw's question is still valid. Using the <> operator is highly specific in its behavior. Is there a chance that you have a case something like "*h*" that is being rejected (or NOT being rejected)? Is there a chance that there might be a space before or after the "*H*" string in the field being compared?

 

[kitty77]

What I mean is when you use one at a time, If field1 <> "*H*" it works exactly how I want but when you combine two conditions together, if does not go to msgbox even though the criteria is met. Both the fields do not have an "H" and the then... msgbox does not come up. But works when I use only one at a time. It's clearly not checking for both fields. that's what I'm looking for...

 

[theDBguy]

What I mean is when you use one at a time, If field1 <> "*H*" it works exactly how I want but when you combine two conditions together, if does not go to msgbox even though the criteria is met. Both the fields do not have an "H" and the then... msgbox does not come up. But works when I use only one at a time. It's clearly not checking for both fields. that's what I'm looking for...

Hi. Pardon me for jumping in, but are you sure it's working? Check this out:

 

[kitty77]

Yes, it is. If there is a better way to check if multiple fields have a condition, I'm all ears...

 

[The_Doc_Man]

OK, try to reverse your logic. This is done using DeMorgan's theorem, which you can look up separately if you wish. The short answer is that you invert the logic by flipping "=" in place of "<>" and "OR" in the place of "AND" - as for example...

IF ( Field1 = "*H*" ) OR ( Field2 = "*H*" ) OR ( Field3 = "*H*" ) OR ( Field4 = "*H*" ) THEN
    'at least one of the fields contains "*H*"
ELSE
    'none of the fields contains "*H*"
END IF

 

[theDBguy]

Yes, it is. If there is a better way to check if multiple fields have a condition, I'm all ears...

Hi. Did you see the image I posted? Unless I am missing something, I was expecting to get a False result, but got True instead? Were you expecting to get True as a result of "hello"<>"*h*"?

 

[jdraw]

Kitty,
Perhaps it's time you showed us what your field1...field4 actually contain.
A sample using your data and exactly what you are comparing would be helpful.

Some testing:
F1 ="*Y*"
?F1 <>"Y"
True
?F1<>"*Y"
True
?F1="*Y*"
True
?F1<>"*Y*"
False
?F1 Like "*Y*"
True
?F1 like "Y*"
False
?F1 like "*Y"
False

Sub tkit()
    Dim f1 As String
    Dim f2 As String
    Dim f3 As String
    Dim f4 As String

    f1 = "*W*"
    f2 = "*X*"
    f3 = "*Y*"
    f4 = "*Z*"

    If f1 <> "*H*" And f2 <> "*H*" And f3 <> "*H*" And f4 <> "*H*" Then
        Debug.Print "No *H*"
    Else
        Debug.Print "an *H* somewhere"
    End If
End Sub

No *H*

 

[kitty77]

OK, try to reverse your logic. This is done using DeMorgan's theorem, which you can look up separately if you wish. The short answer is that you invert the logic by flipping "=" in place of "<>" and "OR" in the place of "AND" - as for example...

IF ( Field1 = "*H*" ) OR ( Field2 = "*H*" ) OR ( Field3 = "*H*" ) OR ( Field4 = "*H*" ) THEN
    'at least one of the fields contains "*H*"
ELSE
    'none of the fields contains "*H*"
END IF

 

[kitty77]

That did it. Makes more sense too. Thanks!

 

[theDBguy]

That did it. Makes more sense too. Thanks!

Hi. Sorry to keep going back to this, but just as jdraw brought it up in Post #2, unless I am missing something very obvious, I am not really sure what you think is working is actually working. Here's what I get in the Immediate Window using the new expression you're using now (which you said also work). I expected to get a True result this time.

 

[jdraw]

DbGuy,

Just tried this
?"Hello" like "*H*"
True

 

[theDBguy]

DbGuy,

Just tried this
?"Hello" like "*H*"
True

That was my point too! Why was the OP using <> or =, when it should have been Like?

 

[The_Doc_Man]

Kitty77 reports that my logic flip worked. If it did then perhaps "LIKE" isn't the issue anyway. I cannot say with certainty why it didn't work in the other format other than the idea of expression telescoping, which might have gotten in the way here.

For the uninitiated, "expression telescoping" is a way for advanced compilers (including VBA) to shorten the evaluation for special cases. A chain of "AND" in VBA is telescoped such that it stops evaluating the first time it hits a FALSE. A chain of "OR" would telescope the first time it hits a TRUE. It is also possible to telescope a chain of multiplication if any of the multiplicands are zero.

 

[jdraw]

Doc,
It seems there may be a few unknowns at work with Kitty's project. It is not clear what exactly is being compared to what and under what conditions. The use of the asterisk within the variable and with an = sign is somewhat rare. I think dbGuy and I are still scratching our heads a little. But if it works for Kitty --all is well.

 

[The_Doc_Man]

jdraw, I don't disagree at all. However, if Kitty77 is happy then we did our jobs.

 

[Cronk]

Excuse for for coming to the party late, but another alternative

if instr(Field1 & Field2 & Field3 & Field4 & "","H") > 0 then
   '---One of the fields contains H