Validation Rule

[Mr_Adams]

I use the formula below to validate entries in excel to look like
#####.#####
or
#####.#####.##

=OR(AND(MID(O6,6,1)=".",NOT(ISERR(VALUE(SUBSTITUTE(O6,".","")))),LEN(O6)=11),AND(AND(MID(O6,6,1)=".",MID(O6,12,1)="."),NOT(ISERR(VALUE(SUBSTITUTE(O6,".","")))),LEN(O6)=14))

Can someone help me with a validation rule for access that will do the same thing

Thank You

 

[Mr_Adams]

I'm getting closer but I could use some help if anyone has any ideas

here is what I have so far

Private Sub ACCOUNT_BeforeUpdate(Cancel As Integer)
    If Len("[ACCOUNT].value") <> 11 Or Mid("[ACCOUNT].value", 6, 1) <> "." Then
        MsgBox "Invalid Account Number", vbCritical, "Error"
        DoCmd.CancelEvent
        Exit Sub
    Else
        Select Case (Mid("[ACCOUNT]", 7, 1))
            Case 3: [TYPE] = "EC"
            Case 4: [TYPE] = "UR"
            Case Else:
            MsgBox "Invalid Account Number"
            Cancel = True
        End Select
    End If
End Sub

But I get my messages "Invalid Account Number" every time and what could I add to allow a second account type of "#####.#####.##"

Any Ideas would be very helpful

 

[Cbrine]

I'm getting closer but I could use some help if anyone has any ideas

here is what I have so far

Private Sub ACCOUNT_BeforeUpdate(Cancel As Integer)
    If Len("[ACCOUNT].value") <> 11 Or Mid("[ACCOUNT].value", 6, 1) <> "." Then
        MsgBox "Invalid Account Number", vbCritical, "Error"
        DoCmd.CancelEvent
        Exit Sub
    Else
        Select Case (Mid("[ACCOUNT]", 7, 1))
            Case 3: [TYPE] = "EC"
            Case 4: [TYPE] = "UR"
            Case Else:
            MsgBox "Invalid Account Number"
            Cancel = True
        End Select
    End If
End Sub

But I get my messages "Invalid Account Number" every time and what could I add to allow a second account type of "#####.#####.##"

Any Ideas would be very helpful

Shot in the dark.


Case "3": [TYPE] = "EC"
Case "4": [TYPE] = "UR"

Since you are working with string values and not numeric.

 

[Mr_Adams]

Case "3": [TYPE] = "EC"
Case "4": [TYPE] = "UR"

It doesn't make it that far in the code.
No matter what is entered it sees the
entry as an "Invalid Account Number"
and I still can't figure out how allow
#####.#####.## as a valid account number

I'm thinking because it's bound I might
need to use
("[Forms]![Invoice Input]![ACCOUNT].value")
instead of just
("[ACCOUNT].value")
or maybe After update instead of before

any ideas

 

[Cbrine]

Sorry didn't notice the first invalid account message.(Although I think the code needs to be changed)
This line looks funky to.

If Len("[ACCOUNT].value") <> 11 Or Mid("[ACCOUNT].value", 6, 1) <> "." Then

Try
If Len([ACCOUNT]) <> 11 Or Mid([ACCOUNT], 6, 1) <> "." Then

or maybe

If Len(me![ACCOUNT]) <> 11 Or Mid(me![ACCOUNT], 6, 1) <> "." Then

I haven't tested any of this.

 

[Mr_Adams]

ok this code works for both account number types of
#####.##### and #####.#####.##

Private Sub ACCOUNT_BeforeUpdate(Cancel As Integer)
If Len([ACCOUNT]) = 14 And Mid([ACCOUNT], 6, 1) = "." And Mid([ACCOUNT], 12, 1) = "." Then
Select Case Mid([ACCOUNT], 7, 1)
            Case 3: [TYPE] = "EC"
            Case 4: [TYPE] = "UR"
            End Select
            Exit Sub
ElseIf Len([ACCOUNT]) <> 11 Or Mid([ACCOUNT], 6, 1) <> "." Then
        MsgBox "Invalid Account Number", vbCritical, "Error"
        DoCmd.CancelEvent
        Exit Sub
    Else
        Select Case Mid([ACCOUNT], 7, 1)
            Case 3: [TYPE] = "EC"
            Case 4: [TYPE] = "UR"
            Case Else:
            MsgBox "Invalid Account Number", vbCritical, "Error"
            Cancel = True
        End Select
    End If
End Sub

Thank You