Need help creating a validation rule. (1 Viewer)

stell

Registered User.
Local time
Today, 01:10
Joined
Jun 14, 2017
Messages
15
So I am trying to create a validation rule that only allows the field to start with certain numbers (it is a short text field). For example, the order number can only start with a 5,8, or 9, if it starts with any other number it should be rejected.

Any advice? Thanks in advance.
 

RuralGuy

AWF VIP
Local time
Today, 02:10
Joined
Jul 2, 2005
Messages
13,826
Use the BeforeUpdate event to enforce your validation. Cancel = True will hold the focus in the current control.
 

MarkK

bit cruncher
Local time
Today, 01:10
Joined
Mar 17, 2004
Messages
8,178
If the field may only start with certain numbers, (maybe it's a prefix to a longer code) then that part of the field is distinct, and should be stored in its own field. Then use a combo that only offers valid choices. Finally, re-concatenate the final value as needed in a query, like....
Code:
SELECT Prefix & "-" & Value & "-" & Suffix As FinalCode
FROM Table
hth
Mark
 

Solo712

Registered User.
Local time
Today, 04:10
Joined
Oct 19, 2012
Messages
828
So I am trying to create a validation rule that only allows the field to start with certain numbers (it is a short text field). For example, the order number can only start with a 5,8, or 9, if it starts with any other number it should be rejected.

Any advice? Thanks in advance.

Run this in the form's BeforeUpdate event:

Code:
Select Case Left(Me!Myfield,1)
    Case "5", "8", "9"
        'Do nothing
    Case Else
        MsgBox "Field may only start with '5', '8' or '9'"
        Cancel = True
        Me.Myfield.SetFocus
        Exit Sub               
End Select

Substitute the actual field name for 'Myfield'

Best,
Jiri
 

RuralGuy

AWF VIP
Local time
Today, 02:10
Joined
Jul 2, 2005
Messages
13,826
FYI, the SetFocus is not necessary as Cancel = True will do this for you.
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:10
Joined
Jan 23, 2006
Messages
15,364
stell,

It would be helpful if you would tell us a little more about this proposed database. Your question seems extremely focused, but there may be options if readers knew more about the database requirement.

You have been give good answers for the specific question, but a validation rule/constraint for one field doesn't exist in isolation. So more info please.

Good luck.
 

Users who are viewing this thread

Top Bottom