Query Issue (DOB)

Question

Hello, hope someone can help, I have a table of peoples details and I want to calculate someone's age from their DOB. I have made a query, put in a calculation, ( Age: DateDiff("yyyy",[DOB],Now()) ) it all works well after numerous checks. But now when I close the database and open it again its asking me for a "Enter Parameter Value" and I cannot open up this query, also I have built a table from this query and the same happens? Can some one please advise??

Answer 1

That expression for calculating the age of a person will sometimes not be accurate, you need to use something more like:

Age: DateDiff ("yyyy",DateOfBirth, Date())+(Date() < DateSerial(Year( Date()), Month(DateOfBirth), Day(DateOfBirth)))

As for your error, you have obviously add a parameter criteria in a query, form recordsource ... that is prompting you.  If this happens at startup, review each database object that is launched at the startup for parameters.  There is no easy way to do this, you have to manually find the source of the issue.

---

Daniel Pineault, 2010-2016 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Hi apmitch,

for an example link you can refer below.

Calculate correct age in MS-Access query

SELECT FirstName, LastName, DateOfBirth, 
DateDiff ("yyyy",DateOfBirth, Date())+(Date() < DateSerial(Year( Date()), Month(DateOfBirth), Day(DateOfBirth))) AS Age,
Date() < DateSerial(Year( Date()), Month(DateOfBirth), Day(DateOfBirth))) AS AddYear 
 FROM Employees;

The results will look like this: 

FirstName	LastName	DateOfBirth	Age	AddYear
john	silver	08/12/58	49	-1
gret	garbo	15/01/49	59	0
le	chen	01/10/69	38	-1
daniel	defo	01/04/78	30	0
maggi	forth	25/02/52	56	0
voich	lutz	30/01/82	26	0

Access displays the Enter Parameter Value dialog box when you open an object that contains an identifier or expression that Access cannot interpret. In some cases, this is the behavior that you want. For example, the creator of the database might have created a query that lets you enter information every time that the query runs, such as a start date or an employee ID number.

To stop the Enter Parameter Value dialog box from appearing, you must inspect any expressions that are associated with the object that you are working with, and find the identifier that is causing the dialog box to appear. Then, you must correct the identifier or the syntax of the expression that contains the identifier. The following procedures provide general guidelines for inspecting the expressions in different types of objects. However, the specific changes that you must make depend on the structure of your database.

References:

Why does Access want me to enter a parameter value?

Use parameters to ask for input when running a query

Use a parameter to make a query ask for input

"Enter Parameter Value" dialog box appears when you run a query, a form, or a report

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Thank you, works fine now, much appreciated.

---

apmitch