Age validation rule 17-65

I need to use a validation rule that checks how old a person is by their date of birth.

Here is what i have so far, 6205 is the number days a 17 is alive, the difference in the date of birth must be more or equal to this, however it must be less that 23725 the number of days a 65 year old is alive.

6205=DateDiff("d",[Date of Birth],[Employment Date])

Any help appreciated


Post your answer or comment

comments powered by Disqus
I' need an "minimum age" validation rule for my "members table" to become member you must be 18 years old.
I have tried this rule:

I need help! I am trying to create a validation rule for a number data type field. My rule would only allow entries to end with the following: .00, .25, .50, .75

The number before the decimal could range anywhere between 1 and 18.

Essentially, I only want users to be able to enter data types such as
1.25 or .25 or 17.75 etc...

Can someone please assist?

I need help making a validation rule so that my discount field will be "no less than 0 and no more than .17"



If I append data from one table into another using a query, I get the following message:

"Test2008...didn't add...15 records due to validation rule violations."

Fine, I can work out why this is.

The problem is that I want to incorporate the append function into a generic VBA procedure. I create the SQL and then use:

Set qdfAppendQuery = dbCurrentDatabase.CreateQueryDef("", strSQL)

The append fails as expected and qdfAppendQuery.RecordsAffected = 0.

The problem is it doesn't appear to generate any trappable error. How can I give the user a clue as to what has happened?

Hello all,

I need help. I have create very cool database for Company X. I have found one mistake about validation rule in Field Properties.

I have create a few field, which some of them have validation rule Is Not Null, and they must not be empty. After enter information in the fields, they are exported into excel. In the end of export job, I have query which clear some fields. Here comes a problem.
When customer want to fill again choosen record, the validation rule does not working any more, and it does not warning that must not be empty.

I would like to create validation rule with Visual basic. I have just started exploring VB, so I will need help about it.

Thanks for help in advance...!

I have a bound combo box that has the numbers 0 through 9 as the options. These numbers are pulled from a table and are associated with other fields (though this is not important). What I am trying to get done is have a pop up box indicating "Are you sure?" if the number 6, 7, 8, or 9 is chosen. Then, once you click ok, you can go on to finish entering data into the form

I originally thought this would fall under the validation rule in my form but now lean more towards VB. My guess is that it needs to be in the After Update event, but I can't figure out the "If" statement I should use. Any suggestions would be greatly appreciated!

i need a validation rule i have a field that needs to get the first three letters from another fields value + 8 numbers

for example

Product = Funtime Frank
ShortCode = Fun12345678

the colours are just there to demonstrate what i mean

this is my validation rule
like Select Left (product,3) From tblblues And "########"

its wrong however i don't know where to go from here


I'm starting out on a new database for my staff to create labels for correspondence folders, and I'm in need of some help.

I have an unbound field that I want a command button to evaluate to check the field before moving onto the report. My first idea was to use a validation rule, but this would be for multiple rules in the same field, which I wasn't sure I could do. I'm trying to get the DB to only accept the following types:

A###### - The letter "A" followed by six digits
D######R - The letter "D" followed by six digits, then the letter "R"
L###### - The letter "L" followed by six digits
WW#### - Two "W" letters followed by 4 digits

I figured using the ElseIf method would work as well, but I'm having difficulty preparing the code:

Sub cmdCreateLabel_Click()

Dim strIncorrect As String

strIncorrect = MsgBox "Please enter only the following: [format examples]" vbOkOnly

If txtAppID (Does Not Equal A######) Then
Msgbox strIncorrect
... Repeat for other types ...
End If

End Sub

My last idea was using the Format property in VBA, but the DB recognized "A" as a literal character.

Any ideas?


Sorry for my lack of terms…

I have a table “purchases” that contains a lookup field. The lookup field displays the field “upc” from the table “red”. “id” is the primary field in the table “red”.

The lookfield “upc” should only be able to except the values greater then 10 but less than 100. Do I come up with a validation rule for this field in this table? If so what is it? Or is a form with vba the way to go…and if so what is the vba?

Thanks for the help

Hi Folks,

Due to a glitch I am working around, I need to create validation rules for certain fields in VBA versus using the validation rule property in the field property. For some reason, this code I am using below isn't working properly ... any ideas would be appreciated!

Private Sub Needs_Assessed_by_Instrument___Employment__Date_Af terUpdate()

If Me.Needs_Assessed_by_Instrument___Employment__Date < #1/1/2011# Or Me.Needs_Assessed_by_Instrument___Employment__Date > Now() Then

MsgBox ("Date too far in the past or in the future, data not saved, please correct!")

Me.Needs_Assessed_by_Instrument___Employment__Date = ""

End If
End Sub

Basically, no matter what date I put in the field, it triggers the message of the date being too far in the past or in the future. I am trying to analyze the date after the field has been updated so that any date OUTSIDE of the range of 1/1/2011 and Today the user gets a notice that the date is an error and has the opportunity to correct it. Once I can get this to work, after the message is displayed to the user IF the date is an error, I will erase that field so that the user must go back to fix it.

I have inventory query with following fields

Product ID , Purchased, Sold,On Order, Available, LocationA, LocationB, LocationC

where locationA, B ,C represent the quantity available in each location.
Available represents the total quantity available in 3 locations

In order details subform, i have the following fields
Product ID, Location ID, QTY, Price, Total, status

Now the problem is , I dont want the user to give a qty more than the qty available in the location selected.
I tried to set validation rule via VBA in after update property of Location ID,,, but it shows an error that i have used visual basic code.

Can you give your idea please, what should i use here, thanks in advanced.

Does anyone know how to set a table level validation rule with VBA?

I created a module based on the instruction on Microsoft's Knowledge Base(KB210385) to create a validation rule to accept only alphabetical characters. The code of the module is given below:

Option Compare Database

Option Explicit
Function IsAlpha(MyString As String) As Integer
Dim LoopVar As Integer
Dim SingleChar As String

LoopVar = 1

If IsNull(MyString) Then
IsAlpha = False
Exit Function
End If

For LoopVar = 1 To Len(MyString)
SingleChar = UCase(Mid$(MyString, LoopVar, 1))
If SingleChar < "A" Or SingleChar > "Z" Then

IsAlpha = False
Exit Function
End If
Next LoopVar

IsAlpha = True

End Function

The module works on my fields but it does not allow any spaces or gaps in the fields which makes it almost useless for my database. So can anyone help me to resolve my problem by giving me the code of the module which will accept alphabets and spaces but not numbers.

Hi everyone!

I have a question concerning the validation. My problem is the following:

I have one table which stores info about Herbs. Herbs can be either annual or perennial. Then another table shows what herbs grow on a particular field. If a herb is perennial, nothing else can grow. But if it is annual, then there can be another herb.

So, on a form which must be in datasheet view, I first enter Herb1 from a combo box. Then, if Herb1 is “annual” I should be able to enter a value for Herb2. If it is “perennial”, this must not be possible.

So far I came up with the following code in the before_update event of herb2:

	Private Sub Herb2_BeforeUpdate(Cancel As Integer)
  Dim findCond As String
  findCond = DLookup("Herbs.[Type]", "Herbs", "Herbs.[HerbName] = Herb1")
  If findCond = "perennial" And Herb2  "" Then
      MsgBox "You cannot add a value", vbExclamation, "Error"
      Cancel = True
  End If
End Sub

I found out that I can not enable or disable the Herb2 control, because in datasheet view the whole Herb2 column will become enabled/disabled, not just the text box for a particular record.

What I don’t like in my solution is that the user is notified AFTER he enters something into the field. Can anyone suggest anything better for this problem? Whereas I would prefer the program to prevent him from accessing this field.

And, IDEALLY, how do I integrate the
(DLookup("Herbs.[Type]", "Herbs", "Herbs.[HerbName] = Herb1" ‘perennial’) or (is null )
as a validation rule of Herb2? It says that there is a syntax error in this statement.

I would really appreciate some suggestions and advice.

Hey guys, I have an error in my Validation Rule Property... I get this error everytime I enter the Reservation Number (autonumber) into the field, to grab the Customer ID.

"The macro or function set to the beforeupdate or validationrule property for this field is preventing access from saving the data in the field"

I have nothing the the BeforeUpdate for this field, the validation rules is:

I only have 11 Reservations in the table... If I put "12" into the ResNoField it will say "Reservation Number Not Found" but 11 brings up the error above.

	Function CheckResNo() As Boolean
    Dim db As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim RNO As Integer
    Dim RetValue As Boolean
    RNO = Forms("Reservation")!ResNoField
    RetValue = False
    Set db = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    rs.Open "Select * From Customer Inner Join Reservation on Customer.CustomerID=Reservation.CustomerID Where
Reservation.ResNo=" & RNO, db
    If Not rs.EOF Then
        Call DataFill3(rs)
        RetValue = True
    Cancel = vbCancel
    End If
    Set rs = Nothing
    Set db = Nothing
    CheckResNo = RetValue
End Function


	Sub DataFill3(rs As ADODB.Recordset)
  Dim fs As FileSystemObject
  Set fs = New FileSystemObject
  Forms("Reservation")!Rents.Enabled = True
  Forms("Reservation")!NewRadio.Enabled = True
  Forms("Reservation")!NewRadio.Value = 2
  Forms("Reservation")!CustIDField.Enabled = True
  Forms("Reservation")!SearchButt.Enabled = True
  Forms("Reservation")!ResNoField = rs!ResNo
  Forms("Reservation")!CustIDField = rs!CustomerID
  Forms("Reservation")!FirstNameField = rs!FirstName
  Forms("Reservation")!LastNameField = rs!Lastname
  Forms("Reservation")!TelephoneField = rs!TelNo
  Forms("Reservation")!StreetField = rs!Street
  Forms("Reservation")!CityField = rs!City
  Forms("Reservation")!ZipField = rs!Zip
  Forms("Reservation")!StateField = rs!State
  Forms("Reservation")!EmailField = rs!Email
  Forms("Reservation")!PicIDField = rs!PicID
  If fs.FileExists(rs!PicID) Then
    Forms("Reservation")!PicID.Picture = rs!PicID
    Forms("Reservation")!PicID.Picture = "c:default.jpg"
  End If
End Sub

Thanks for the help guys!

Good Day! I just want to ask since there are two ways to which we can implement our validation rules in general; One in Table and another in a form, how do you usually implement it? Are you using both form and table validation rule capability or just one of them and make it as solid as possible? Because as of now, I have validation rules for both tables and my forms and i find it redundant but at the same time anticipating guilt feelings if i remove all of my validation rule on the table level because i've read several articles regarding validations that veterans strongly suggests it to be implemented on the table level. But in my experience, i find it more reliable to implement all of my validation rules on the form level because most of the time the table level rule bypasses some of the transactions that are not meant to be saved. So i had to repeat the validation code in the form level just to remind access 2007 of what the conditions are before saving the said transaction.

In conclusion, I have lots of redundant validation rules between my tables and forms. I'm not sure if this can affect my database efficiency somehow or maybe in the long run but personally i just want to do it the right way. So i'm sorry i had to ask this question just to clear things up a bit. I really need your opinion on this. Thank you in advance.

I have begun desiging a database for my company that stores information about parts we produce for our customers. I am relatively new to Access and am using Access 2010.

Sometimes customers parts have dimensions given in inches, while other times they are given in millimeters. I would rather have a query do the conversion to inches than the person doing it prior to inputting data. From there, all other calculations I do will be based off of the dimensions in inches.

Here is how I was planning on handling this:

I have a table with the following fields:
Part Number

When the query is ran, I use an Iif to see whether MM was inputted and if the user inputted in MM, it creates a new field that converts to inches.

However, here is where I need help. I would like a validation rule to only allow Thickness(In) or Thickness(MM) to be inputted. For instance, if the user inputs a value for Thickness(In), I would like Access to prevent the user from inputting Thickness(MM) (unless Thickness(In) is null or =0).

How can I accomplish this?

Hi - I want to set-up a data validation rule on my table that warns the user when a duplicate has been entered, but does not prevent it.

I can do this in Excel, whereby I have a custom message warning the user.

How do I replicate in excel?


Hello everyone. So I am new to Access and self taught. The depth of my knowledge comes from forums, youtube, and Microsoft help pages. I am in no way a programer, but am somewhat tech savvy.

With that in mind, I have created a database that captures information of students who participate in certain programs and their involvement in those programs. I have a student information table that is used to capture student information and a form that is used for the entry of that student information and to look up students. I have run into an issue with the LastName field of the student. This field is connected to the combo box that is used to look up the student and pull up all of the student's information if they are in the database.

The issue comes in when I look up a student, verify all of their information and then click the New Record button which creates a new record likewise a blank form for entry. In the table, I have set this last name field as required, so that blank records are not created and stored every time the new record button is clicked and no information is added (this was happening before I set these parameters). Unfortunately that did not solve the problem.

What Access has decided to do now, is take the primary key from the last student that was searched in the combo search box, and store that primary key number in the last name field of a new record that is created. It does not show the primary key filled into the last name field on the form, but when I view the table there are stored records with nothing but a primary key in the last name field. This is obviously not information that I want or need and I want to stop it from happening.

I though that if I set a Validation Rule for the last name field that the problem would be solved. This is where I need your help. I have searched for validation rules and found a few but none that meet my exact needs, and I am not sure how to write one myself. I need a validation rule that would all all characters but numbers. Some individuals have - or spaces in their last names as well as periods and such and I want these characters to be valid. If I have a validation rule that allows everything but numbers I think this would solve the problem while leaving my table reliable and valid, but I do not know how to write a validation rule like this or even if it is possible.

Sorry for such a long post, but I feel as though it is necessary to understand where the problem is coming from. Maybe someone will have a fix to the underlying problem instead of simply the cover-up.

Thank you so much!!!!!!!

Hello everyone.

I am (kind of) new to Microsoft Access and have a question about making validation rules more complex.

I am making a quality control database with 3 tables. One containing the product and their unique codes, the 2nd with their unique lot numbers for each different production run, and the 3rd with all the required quality control data for each product.

Each different product has different required specifications. For example, Product AB-544 needs a value between 11-20 while product RY-233 needs a value between 21-30.

My question is: Is there a method of defining different validation rules for different product codes? I want the QC tech entering the data to only be able to enter in quality data that is within the required specification defined by the product code. If the current product is AB-544 he should only be able to enter in 11-20 but if he is entering in data for RY-233 he should only be able to enter 21-30.

If this is possible without knowing visual basic (which I am learning) that would be awesome. Any help is greatly appreciated!

Hi all. I am new to access. Am using 2002 version and am self taught. I am creating my first real database and have been asked to perform a function i am not familiar with.

I have two fields that require attention. One is a check box. If this check box is selected i need to have a validation rule stating that the second field requires data before the use can proceed to enter any other data.

any help would be appreciated, thanks.

=Iif([ApproveWorkUnsupervised]=”Y” And [GoneToWork]=”Y” And [Hours Worked This Week]>0,” ”,Iif([ApproveWorkUnsupervised]=”Y” And [GoneToWork]=”N” And [Hours Worked This Week]= 0,” ”,Iif([ApproveWorkUnsupervised]=”N” And [GoneToWork]=”N” And [Hours Worked This Week]=0,” ”)))

This Validation Rule gives an error on the second Iif condition.

Anyone know if this is the correct way to code the Iif


I am trying to create a validation rule on a text box so that the user can only enter a number that is in a .5 increment.

ie: 1.5 is OK
2.0 is ok
2.6 is NOT OK

Here is what I have in the validation rule property field....
Like "*.0" Or Like "*.5"
but it will only accept numbers that end in .5..... if the number ends in .0 it rejects it

any ideas ....


I am working in a Continuous form.
One control in this continuous form is Combo Box.
As The select new value in this combo box so new record is added to this form, I want to use a validation rule on this combo box so the user will not be able to select a new value that was already selected before in this continuous form.
So please help me writing the validation rule that will prevent repeating the same value in this continuous form.


Not finding an answer? Try a Google search.