Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-08-2017, 01:29 PM   #1
stell
Newly Registered User
 
Join Date: Jun 2017
Posts: 15
Thanks: 3
Thanked 0 Times in 0 Posts
stell is on a distinguished road
Need help creating a validation rule.

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.

stell is offline   Reply With Quote
Old 12-08-2017, 03:02 PM   #2
RuralGuy
AWF VIP
 
RuralGuy's Avatar
 
Join Date: Jul 2005
Location: @ 8300' in the Colorado Rockies
Posts: 13,821
Thanks: 7
Thanked 311 Times in 301 Posts
RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice
Re: Need help creating a validation rule.

Use the BeforeUpdate event to enforce your validation. Cancel = True will hold the focus in the current control.
__________________
(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please post back to this Forum so all may benefit.
Teaching is not filling a bucket but lighting a fire.
RuralGuy is offline   Reply With Quote
Old 12-08-2017, 03:36 PM   #3
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 12-08-2017, 10:16 PM   #4
Solo712
Newly Registered User
 
Solo712's Avatar
 
Join Date: Oct 2012
Posts: 739
Thanks: 14
Thanked 126 Times in 123 Posts
Solo712 will become famous soon enough
Re: Need help creating a validation rule.

Quote:
Originally Posted by stell View Post
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
__________________
Dyslexics of the world untie !
Solo712 is offline   Reply With Quote
Old 12-09-2017, 05:23 AM   #5
RuralGuy
AWF VIP
 
RuralGuy's Avatar
 
Join Date: Jul 2005
Location: @ 8300' in the Colorado Rockies
Posts: 13,821
Thanks: 7
Thanked 311 Times in 301 Posts
RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice
Re: Need help creating a validation rule.

FYI, the SetFocus is not necessary as Cancel = True will do this for you.
__________________
(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please post back to this Forum so all may benefit.
Teaching is not filling a bucket but lighting a fire.
RuralGuy is offline   Reply With Quote
Old 12-09-2017, 05:35 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,238
Thanks: 93
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Need help creating a validation rule.

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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Reply

Tags
field , first number , rule , string , validation

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Validation Rule NBaker Forms 3 08-08-2013 10:57 AM
Validation Rule Help dcollard23 Forms 3 02-10-2011 07:49 AM
Creating a Validation Rule deejabram General 6 07-22-2010 05:11 AM
validation rule Mik3 Forms 3 07-28-2003 07:23 AM
Validation Rule Haytham Forms 1 11-12-2001 04:37 PM




All times are GMT -8. The time now is 04:48 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World