none
Query Issue (DOB) RRS feed

  • 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??
    Saturday, April 15, 2017 7:45 AM

Answers

  • 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

    Saturday, April 15, 2017 11:12 AM
  • 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 MSDNFSF@microsoft.com.

    Monday, April 17, 2017 5:22 AM
    Moderator

All replies

  • 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

    Saturday, April 15, 2017 11:12 AM
  • 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 MSDNFSF@microsoft.com.

    Monday, April 17, 2017 5:22 AM
    Moderator
  • Thank you, works fine now, much appreciated.

    apmitch

    Tuesday, April 18, 2017 7:28 AM