Adding code to Access for Specific Area Codes for a State

[KingHavocIV]

Hi All,

Can you see anything wrong with the below code? I'm trying to make sure that if the State in the record is VA, that the area code is either 703 or 804. When I test, no matter what I change the first 3 integers of the phone number to, I get the error msg box. Even if it's 703 or 804, I still get the error box. Any help would be appreciated. Thanks.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    'Verify the correct area code for VA
    Dim AreaCode As Integer
    If Not IsNull([State]) And Not IsNull([Phone]) Then
        AreaCode = Val(Left([Phone], 3))
        Select Case [State]
            Case "VA"
                If AreaCode <> "703" Or AreaCode <> "804" Then
                    DoCmd.CancelEvent
                    MsgBox "Area Code must be 703 or 804"
                    Phone.SetFocus
                End If
        End Select
    End If
    
End Sub

[ADezii]

Just a slight change in Logic should do the trick:

'Verify the correct area code for VA
Dim AreaCode As Integer

If Not IsNull([State]) And Not IsNull([Phone]) Then
  AreaCode = Val(Left([Phone], 3))
Else
  Exit Sub
End If

Select Case [State]
  Case "VA"
    Select Case AreaCode
      Case 703, 804
        'do nothing
      Case Else
        MsgBox "Area Code must be 703 or 804"
          Me![Phone].SetFocus
      End Select
  Case Else
    'States other than 'VA'
End Select

P.S. - The Expression

If AreaCode <> "703" Or AreaCode <> "804"

will never evaluate to TRUE even if AreaCode = 703 or 804 since it cannot be equal to both at the same time.

[Seth Schrock]

Your problem is in the line

If AreaCode <> "703" Or AreaCode <> "804" Then

Because you used the OR comparison, only one side has to be true for the If/Then to follow the true side. So if you enter 703, the first side is false but the second side is true, so your code will produce the message box. If you used the AND comparison, then both sides would have to return true for the true path to be taken. Thus, if you enter 703, then the first side returns false and makes the whole test return false.

[KingHavocIV]

Thank you ADezii, That worked perfectly. I don't know why the <> didn't work, but I'm glad you found a way to make it work. Thanks again.

[KingHavocIV]

Thank you Seth for the insight. That worked with my original code. I guess I need to be more careful with my comparison operators. Thanks for pointing that out.

[ADezii]

I don't know why the <> didn't work

It is simply a matter of Syntax.

'************** Code Intentionally omitted **************
Select Case [State]
  Case "VA"
    If (AreaCode <> 703) And (AreaCode <> 804) Then
      MsgBox "Area Code must be 703 or 804"
        Me![Phone].SetFocus
    End If
  Case Else
    'States other than 'VA'
End Select

will work.