trying to set up array - get type mismatch

[osupratt via AccessMonster.com]

i have a field on my form called FTNumber. trying to get a message box on the
after update to where:

if mid(FTNumber,1,1)<> "A" Then
MsgBox "You entered a Ticket Number that is not of a recognized format;
ensure the Ticket Number is correct", vbInformation, "Notice"
End If

this works fine. the issue is i have other values that are also a good format.
"C","D","E","F". when i try the:

if mid(FTNumber,1,1)<> "A" or if mid(FTNumber,1,1)<> "C"

it doesn't recognize the "C".

when i set up an array i get a type mismatch. not sure if there is just a
simple way that i'm not seeing to accomplish what i want. thanks.

 

[Rob Parker]

Not sure what this has to do with arrays and type mismatches, but there's a
couple of problems with your second If statement:

First, there should not be an "If" after the "Or".

Second - and the cause of the problem - is that if you evaluate two
different "not equal" conditions for the same variable, at least one of them
must be true (the variable cannot have two different values at the same
time), and so the combined condition will be true, and the msgbox will
always be shown. To test for one of multiple false conditions, you need to
test that none of them are true, as follows:
If Not (mid(FTNumber,1,1) = "A" Or mid(FTNumber,1,1) = "C") Then

HTH,

Rob

 

[Dirk Goldgar]

i have a field on my form called FTNumber. trying to get a message box on
the
after update to where:

if mid(FTNumber,1,1)<> "A" Then
MsgBox "You entered a Ticket Number that is not of a recognized format;
ensure the Ticket Number is correct", vbInformation, "Notice"
End If

this works fine. the issue is i have other values that are also a good
format.
"C","D","E","F". when i try the:

if mid(FTNumber,1,1)<> "A" or if mid(FTNumber,1,1)<> "C"

it doesn't recognize the "C".

when i set up an array i get a type mismatch. not sure if there is just a
simple way that i'm not seeing to accomplish what i want. thanks.

You could do this:

Select Case Mid(FTNumber, 1, 1)
Case "A", "C", "D", "E", "F"
' These are okay, do nothing
Case Else
MsgBox _
"You entered a Ticket Number that is not of a " & _
"recognized format; ensure the Ticket Number " & _
"is correct", _
vbInformation, _
"Notice"
End Select

There are other ways to do it; for example,

If InStr("ACDEF", Mid(FTNumber, 1, 1)) = 0 Then
MsgBox _
"You entered a Ticket Number that is not of a " & _
"recognized format; ensure the Ticket Number " & _
"is correct", _
vbInformation, _
"Notice"
End If

 

[Marshall Barton]

i have a field on my form called FTNumber. trying to get a message box on the
after update to where:

if mid(FTNumber,1,1)<> "A" Then
MsgBox "You entered a Ticket Number that is not of a recognized format;
ensure the Ticket Number is correct", vbInformation, "Notice"
End If

this works fine. the issue is i have other values that are also a good format.
"C","D","E","F". when i try the:

if mid(FTNumber,1,1)<> "A" or if mid(FTNumber,1,1)<> "C"

it doesn't recognize the "C".

when i set up an array i get a type mismatch. not sure if there is just a
simple way that i'm not seeing to accomplish what i want. thanks.

After Rob has straightened out your logic, let me suggest an
simpler way to do that:

if Left(FTNumber,1) Like "[!ACDEF]" Then
MsgBox "You entered ...

 

[Rob Parker]

Hi Marsh,

Thanks for the follow-up. I expect that's what the OP was referring to when
he included "array" in his subject line. And if I had a solid grasp of
regexps, I'd have done it that way; sadly, I haven't mastered those yet ;-)

Rob

i have a field on my form called FTNumber. trying to get a message
box on the after update to where:

if mid(FTNumber,1,1)<> "A" Then
MsgBox "You entered a Ticket Number that is not of a recognized
format; ensure the Ticket Number is correct", vbInformation, "Notice"
End If

this works fine. the issue is i have other values that are also a
good format. "C","D","E","F". when i try the:

if mid(FTNumber,1,1)<> "A" or if mid(FTNumber,1,1)<> "C"

it doesn't recognize the "C".

when i set up an array i get a type mismatch. not sure if there is
just a simple way that i'm not seeing to accomplish what i want.
thanks.

After Rob has straightened out your logic, let me suggest an
simpler way to do that:

if Left(FTNumber,1) Like "[!ACDEF]" Then
MsgBox "You entered ...