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]

Any help would be great

Thank You

 

[Cbrine]

Any help would be great

Thank You

Are you referring to entries in a table, query parameter, or form control entry? Each one will have a different way of working with it.

 

[Mr_Adams]

Are you referring to entries in a table, query parameter, or form control entry? Each one will have a different way of working with it.

Entries directly in a table or form control entry for that table

 

[Cbrine]

Do you want to just change how they appear in reports, and queries, or control what is entered into the field?

 

[Mr_Adams]

I need control what they enter into the field.

also, once they enter a correct number in the field I need another field to populate automatcally

i.e if they enter 11111.31111 on the form or in the table the three indicates a revenue account and EC shouls appear in the Account Type field
if they enter 11111.41111 the 4 indicates credit to budget and UR should appear in the account type field.

then in my query criteria I can use EC or UR for the reports

 

[Cbrine]

Ok,
1. Open your table design view
2. Click on the field you want to limit entry to.
3. Click on the input mask in the bottom properties
4. Enter #####.L#### in the field provided.

The user will only be able to enter numeric characters for the # and alpha characters for each L. This should carry through to your forms.

I don't beleive there is any way to default an entry in another field using the tables, although it is possible on a form.
My suggestion would be that you don't worry about the defaulting since you know the rule to ID each type of entry. You can build a custom field, in your queries, that would ID the type of entry.

Type: iif(mid(FieldName,5,1)="4","UR","EC")

Hope this helps.

 

[Mr_Adams]

the account number is all numeric.
it is the number after the first decimal that determines what type of account it is
#####.#####
If it is a three the account type is EC any other number and the account type is UR
there are no letters in the account
the basic account number looks like this
#####.##### (no decimal at the end)
but
#####.#####.##
is also valid

This is why I am having the problem. I don't believe an "or" statement can be used in the input mask to allow for 2 types of entrys
I need a validation rule in the tables design mode

 

[Cbrine]

Sorry Mr_adams,
I finally see your problem, I messed around with it as well, and was not able to come up with a solution for table entry value. Does the data need to be entered into the table directly? You could do this on a form control by evaluating the data in a VBA expresion? The form would always need to be used for data entry into these fields, or by someone who knows the setup?

 

[Mr_Adams]

I was thinking it might be better to use the form for just that reason.
plus I would be able to populated The Account type field on the form with EC or UR based on the account number entered this way as well.
The only problem is that I'm not familur enough with VB to accomplish this on my own.

If anyone out there could help I would appreciate it.

Thank You

this is the code I use in excel to accomplish the account number and type issue, but it only works for #####.##### not #####.#####.##
and it doesn't work in access

Private Sub TextBox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Len(TextBox5.Value) <> 11 Or Mid(TextBox5.Value, 6, 1) <> "." Then
MsgBox "Invalid Account Number"
TextBox5.Value = vbNullString ' vbNullString is the same as ""
TextBox6.Value = vbNullString
Cancel = True
Exit Sub
Else
Select Case Mid(TextBox5.Value, 7, 1)
Case 3: TextBox6.Value = "EC"
Case 4: TextBox6.Value = "UR"
Case Else:
MsgBox "Invalid Account Number"
TextBox6.Value = vbNullString
Cancel = True
End Select
End If
End Sub