I realize this thread is many years old but I am replying for anyone that comes across this in the future.
The solution posted by Taruz will not actually work as "Not" cannot be used in this way.
The workaround is as follows:
Code:
Select Case MyVar
Case String1, String2, String3, etc
Test = False
Case Else
Test = True
End Select
thanks for spotting that error that nobody else noticed in the past 7 years! For info, this would also SOMETIMES work....BUT NOT RELIABLY - see posts 6 & 7
Code:
Select Case MyVar
Case Is <>String1, String2, String3
Test = True
Case else
Test = False
End select
This didn't look like it would work to me so in a brief test:
Code:
Dim strOne As String, strTwo As String, strTest As String
strOne = "one"
strTwo = "two"
strTest = "two"
Select Case strTest
Case Is <> strOne, strTwo
MsgBox "True"
Case Else
MsgBox "false"
End Select
returns True, should be False.
Case Is <> strOne, Is <> strTwo
also returns True. In essence, the comma is an "OR", and from techonthenet: "Once a condition is found to be true, the Case statement will execute the corresponding code and not evaluate the conditions any further." Thus as soon as the test value wasn't equal to the first variable, Access returns true and moves on.
hi Paul
That's interesting.
Before I posted, I had tested with this:
Code:
Select Case Me.cboArea
Case "GI", "BD"
MsgBox "This postcode area is not used for geographical addresses", vbCritical, "Not available"
[B] Case Else[/B]
ShowControls True, "D"
Me.cboDistrict.Requery
Me.cboDistrict = ""
Me.lblDistrict.Caption = ""
Me.cboDistrict.SetFocus
Me.cboDistrict.Dropdown
End Select
I changed the Case Else to Case Is <>"GI", "BD"
It still worked exactly as before
So I ran yours and sure enough it 'wrongly' gave True
Now what I think is happening is indeed explained by what you wrote.
In your example the Case Is statement is looking for <> strOne OR equal to strTwo which is indeed true
I modified it to:
Code:
Select Case strTest
Case strOne, strTwo
MsgBox "false"
Case Is <> strOne, strTwo
MsgBox "True"
End Select
It gave false for reasons that should be obvious
So I'm going to withdraw my alternative suggestion as its too far too easy to give incorrect output
In some ways this is a bit like doing Dim X, Y, Z as String which of course makes X & Y both variants
