Related Topics

Calculating an age with DateDiff

Calculating Age
I have been using a bit of code in a form to calculate the exact age from a Date of Birth control.

If IsNull(txt_DOB) Or IsNull(txt_Customer_ID) Then
Exit Sub
End If
txt_Age = DateDiff("yyyy", [DOB], Date) + (DateSerial(Year(Date), Month([DOB]), Day([DOB])) > Date)

It works fine on one computer, but for some unknown reason, when I run it on a different computer, I get this error (with Date highlighted)

Compile error:

Can't find object or library


calculating age (years and months) from birthdate
I am trying to build school database in which I would like to calculate the student's age from their date of birth (dd/mm/y). none of the suggestions I found on google yielded the desired outcome of age in years and months.


automating age calcs
I have a table with fields for DOB and Age.

On the form I would like to automate the age based on the DOB. I have this formula that was in an unbound field and worked, but I cant get it to work on the bound field:

=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

Additionally, I dont always have the DOB, so I want to be able to type an age in too, (however the priority should be to calc the age if the dob exists).

Please could you


Age Calculation
I have several databases that I need to calculate the ages for. This specific database has the age calculation in the "consumers" table.

The code that I have is "Expr1: DateDiff("y",[Me].[BirthDate],DateAdd(6,"m",[Me].[CurrentDate]))". I have this to update the age "BeforeUpdate" in the form.


Query field has no data
Access 2007

I have a field 'Age' in a Form that automatically derives the age from another field in the same Form, 'Date of Birth.' When I create/run a query where I want to show the age relevant to each record the date of birth field is shown but the 'age' field has no data showing? I suspect it may be something to do with the fact that the age field is an automatic calculation ratherthan a numerical entry.

I would though very much like to perform the simple task of showing the age per field.

The field 'Age' in the form is a calculated field with the following in the Control Source of the Property Sheet:

=DateDiff("y",[Date of Birth],Now())+Int(Format(Now(),"mmdd")


Calculating length of stay in form using Datediff
I'm designing a form and I want to calculate how long our hotel tenants stay. So I have the form open and I am in the property sheet under the data tab. I am using the expression builder, and I see DateDiff has this format: DateDiff (interval, date1, date2, firstweekday, firstweek)

I put in this: DateDiff("d",([End Date],Date())-[Start Date])

I'm getting a wrong number of arguments error. I was also getting another error last night related to the "d" portion of the equation. Obviously I am pretty new and unsophisticated at this stuff so I thought I would check with the experts here for some


How to validate dynamically generated text box
here is the problem:
I have two text boxes, DOB (Date of Birth) and Age.
Age is not gotten from the table, instead it uses this formula in the Control Source: "=DateDiff("yyyy",[DOB],Date())" in order to calculate the age in years.
This works beautifully, except I need to validate the Age box using "<=14 And >=19" but this validation is ignored because the information is generated dynamically.

Can anyone please suggest a way of making this work, or possibly another way of approaching the problem.

I don't have very much experience with Access, so please be gentle


Calculating Age from DOB
In the form I have designed, I want the Age to be calculated and printed side-by-side when the DOB is entered.

How shall I go about it


Calculating Age from DOB
In the form I have designed, I want the Age to be calculated and printed side-by-side when the DOB is entered.

How shall I go about it?


Age Calculation
I have the following sample field and record

Age | ApprovedDate
40 | 15-05-2000

Age-number field that shows the current age in 2010
Approveddate - date field when the application was approved.

How to calculate the age of the applicants at the point of approval. In the above example the age would be 30.


Count records between dates
I have a db containing contacts with a birth date field (call it BDate). I want to count the number of contacts falling within given age ranges. For example, the number of contacts between ages 20 and 24, the number between ages 25 and 29, and so on.

Building a query that calculates ages from Now using DateDiff for a single age range and producing a count of the records within that range is not a problem. What I can't get access to do is produce results for multiple age ranges in the same query


Invalid use of Null Error
below I have some code that calculates the age from their brirth date. I keep getting the Invalid use of Null Error when I insert a DOB and the age is already dispalyed. Does anyone know how to correct this?

Dim lngAge As Long
Dim dteBirthdate As Date
dteBirthdate = Me.DOB.Value
If Not IsNull(Me.DOB.Value) Then
'If Not IsNull(Me.Age.Value) Then

' Make sure passed-in value is a date.
If Not IsDate(dteBirthdate) Then
dteBirthdate = Date
End If

' Make sure birthdate is not in the future.
' If it is, use today's date.
If dteBirthdate > Date Then
dteBirthdate = Date
End If

' Calculate the difference in years between today and birthdate.
lngAge = DateDiff("yyyy", dteBirthdate, Date)
' If birthdate has not occurred this year, subtract 1 from age.
If DateSerial(Year(Date), Month(dteBirthdate), Day(dteBirthdate)) > Date Then
lngAge = lngAge - 1
End If
CalcAge = lngAge
Me.Age.Value = lngAge
Else
MsgBox "The age is already determined, delete the age and then add the date of birth


Query age based on a calculation
Access 2007

I have a field 'Age' that automatically derives the age from another field 'Date of Birth.' However when I create/run a query where I want to show the age relevant to each record the age field is blank? I suspect it may be something to do with the fact that the age field is an automatic calculation rather than a numerical entry.

I would though very much like to perform thesimple task of showing the age per field.


Extract age profiles of people
I have a database of people with various details about them, including their date of birth, in one table.

I need to extract a report from this database which shows an age profile of the people that are in it - maybe like a bar chart or perhaps a list of the age ranges like:

Age 10-20: 15 people
Age 20-30: 19 people
Age 30-40: 30 people


Display Age in Years, Months, Days
I have created a unbound text box with

=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))

to display the age of the client.

Is there a way to display the results to say if your birthdate is March 17, 1978 your age is "33 years 10 months 1 days


Calculating datediff using vba code w/ 2 consecutive rows
here is what I am trying to do:
Using Access 2000, 2007 and 2010, I am trying to calculate the datediff, "n" between two different fields in two consecutive rows and put the value in a separate field in the first row using a vba function or module in Access.
Here is pseudo code:
If ([table1].[(row+1) field2]) = ([table1].[this row field2])
Then [This row Field5]=Datediff("n",[this row field 4],[next row field 3])
basically Format((Datediff("n",[Start time],[StopTimeNextRow])), "h:nn")
But how do I read in the next row using vb code?
What would the function or module be coded from start to finish?


Get The AGE
I am looking for help for my form. Here is the scenario:

I have a table field name= DOB, AGE

In my form I would like to have a form field name = DOB, AGE

Where DOB, is for entering a person Birthday (input mask = 99/99/0000;0;_)

On field AGE, I would like to have a formula that will automatically calculate and show the age of a peson based on the date entered in the DOB. This age value will then be automatically saved in the TABLE field called AGE.

Please help as I am just trying to learn ACCESS.


Age Calculation
I've used a formula to calculate age in a query. The only formula that works is one that uses the "now" feature. I want to calculate the age of a person based on a defined date. For example: I need to know how old a person is on or before May 1, 2012. This is a youth athletic association so it has to be precise. We usually print registration information in January. This formula works, but I'm not sure where to put "5/1/2012".

CalAge: DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB


#Name? error showing in Text box.
When I sent a copy of my database to another person (zipped the file, he extracted the files, he saved it on his desktop) there is a field on one of the forms that determines someone's age based on the date given.

The field computes properly on my laptop and on another desktop here at the house, but when he opens it on his computer the Age field on the form displays as : #Name?

Here is the expression entered for the Control Source, I had him check his, and it is the same:
=DateDiff("yyyy",[DoB],Now())+Int(Format(Now(),"mmdd")


Calculate Age field
I am attempting to calculate the age in years. I added the field to the form after I split the database into a Server/Client format. I am using 2010.

I placed this into the Control field

txtDOB = Date of birth (bound field)
txtAge | = Unbound | (does not have a field in the table)

=DateDiff("y",Year([txtDOB]),Year(Date()))

Thinking this should actually be a query and have that as the control source but not sure how to write that up in sql.