When User Attempts To Submit Form My Select Case Statement DOES NOTWORK!!!!

[R Tanner]

Hi,

When I run the following select case statement on my form, the
messagebox displays when it SHOULD NOT. I am very frustrated about
this. The Adj is 4 and the TS is 12. What in the heck am I doing
wrong???

Select Case Me!TS.Value
Case Is <> (10 Or 15 Or 17 Or 21 Or 22 Or 23 Or 26)
Select Case Me!Adj.Value
Case Is <> (2 Or 3 Or 4 Or 6 Or 7 Or 9)
MsgBox "You have selected this ticket involves an
adjustment which had to have been completed by the Operations " & _
"department. Please either have an Operations
Specialist document this ticket or change the 'Resolved By' " & _
"field to match an Operations Specialist.",
vbInformation, "Workflow Validation"
Exit Sub
End Select
End Select

 

[Tom van Stiphout]

On Thu, 18 Dec 2008 06:14:23 -0800 (PST), R Tanner

It SHOULD. Here is why. Put this in the Immediate window:
?(10 Or 15 Or 17 Or 21 Or 22 Or 23 Or 26)
31

?(2 Or 3 Or 4 Or 6 Or 7 Or 9)
15

As you can see your expression doesn't do what you expected, but is
applying a bitwise Or. It's all legit from VBA's point of view.

Armed with that knowledge, can you fix it and post a new version?

-Tom.
Microsoft Access MVP

 

[MikeB]

I would start with just a conventional list 10 ,15, 17, 21, 22, 23, 26 and then
go to Case Else in both Selects

 

[R Tanner]

On Thu, 18 Dec 2008 06:14:23 -0800 (PST), R Tanner


It SHOULD. Here is why. Put this in the Immediate window:
?(10 Or 15 Or 17 Or 21 Or 22 Or 23 Or 26)
31

?(2 Or 3 Or 4 Or 6 Or 7 Or 9)
15

As you can see your expression doesn't do what you expected, but is
applying a bitwise Or. It's all legit from VBA's point of view.

Armed with that knowledge, can you fix it and post a new version?

-Tom.
Microsoft Access MVP

I'm sorry Tom, I still have no idea what it's doing though. I have no
idea what you mean by 'a bitwise Or'.

 

[MikeB]

I'm sorry Tom, I still have no idea what it's doing though. I have no
idea what you mean by 'a bitwise Or'...

http://en.wikipedia.org/wiki/Bitwise_operation

 

[R Tanner]

I'm not sure it this has been resolved or not. But MikeB's first post was
correct. Just use a comma separated list.

Select Case InputBox("value")
Case 1, 2, 3, 4
MsgBox "1234"
Case 5, 6, 7, 8
MsgBox "5678"
Case Else
MsgBox "else"
End Select

It SHOULD. Here is why. Put this in the Immediate window:
?(10 Or 15 Or 17 Or 21 Or 22 Or 23 Or 26)
31

?(2 Or 3 Or 4 Or 6 Or 7 Or 9)
15

As you can see your expression doesn't do what you expected, but is
applying a bitwise Or. It's all legit from VBA's point of view.

Armed with that knowledge, can you fix it and post a new version?

-Tom.
Microsoft Access MVP
[quoted text clipped - 16 lines]

End Select
End Select

okay thank you...Good deal...I was trying to change the option compare
statement at the beginning of the module to see if that would work but
it wasn't...

 

[R Tanner]

I'm not sure it this has been resolved or not. But MikeB's first post was
correct. Just use a comma separated list.

Select Case InputBox("value")
Case 1, 2, 3, 4
MsgBox "1234"
Case 5, 6, 7, 8
MsgBox "5678"
Case Else
MsgBox "else"
End Select

It SHOULD. Here is why. Put this in the Immediate window:
?(10 Or 15 Or 17 Or 21 Or 22 Or 23 Or 26)
31
?(2 Or 3 Or 4 Or 6 Or 7 Or 9)
15
As you can see your expression doesn't do what you expected, but is
applying a bitwise Or. It's all legit from VBA's point of view.
Armed with that knowledge, can you fix it and post a new version?
-Tom.
Microsoft Access MVP
Hi,
[quoted text clipped - 16 lines]
End Select
End Select

okay thank you...Good deal...I was trying to change the option compare
statement at the beginning of the module to see if that would work but
it wasn't...

I do have one question if anyone is still watching this post...I used
'Case Is <> 2, 3, 4' and it did not work the same as 'Case 5 ,6 ,7'.
What is the difference between these two?

 

[R Tanner]

The problem with saying:

Case Is <> (10 Or 15 Or 17 Or 21 Or 22 Or 23 Or 26)

.. is that if your value is 17, it's true on the very first evaluation: 17
<> 10? The same goes for every number other than 10. I know what you meant,
and you know what you meant, but the code did exactly what it was supposed to.
I assume you were thinking kind of like a SQL statement (except it would
still require AND's instead of OR's to work properly). However, the CASE
statement is evalutating it differently than a SQL statement.

What if you approach it from the affirmative instead? Doesn't it work then?

Select Case Me!TS.Value
Case 10, 15, 17, 21, 22, 23, 26
Exit Sub
Case Else
Select Case Me!Adj.Value
Case 2, 3, 4, 6, 7, 9
Exit Sub
Case Else
MsgBox "You have selected this ticket involves an ...",
vbInformation, "Workflow Validation"
Exit Sub
End Select
End Select

On Dec 18, 10:56 am, "tkelley via AccessMonster.com" <u47368@uwe>
wrote:

[quoted text clipped - 38 lines]

statement at the beginning of the module to see if that would work but
it wasn't...

I do have one question if anyone is still watching this post...I used
'Case Is <> 2, 3, 4' and it did not work the same as 'Case 5 ,6 ,7'.
What is the difference between these two?

Yes, this is what I did. I approached it from the affirmative and it
worked fine...for some reason I had it in my head to approach it from
the other way though...