Parameter query to return clients in particular age

[claine]

Hello

I am using Access 2010. I've calculated the age of clients by creating a new field with Age: Year(Now())-Year([D O B]) but I cannot figure out how to use a parameter query to return the age of the clients between age 20 and 30, 30 and 40, 40 and 50 etc. Any tips?

Charlotte

 

[namliam]

put "Between 20 and 30" on the age column?

 

[Chrafangau]

Assuming you want standard parameter popups each time the query is run, put "Between [Minimum Age:] AND [Maximum Age:]" in the 'Criteria' row of your field.

 

[claine]

Thank you Chrafangau. I believe this to be the criteria I need but I am still having problems:

I have a D O B field so I created a Age field using Age: Year(Now())-Year([D O B]), which when run returns all the clients ages. However when I use the [Minimum Age:] AND [Maximum Age:] in the Age column, I get an error message telling me:

the expression is typed incorrectly or it is too complex to evaluate.

Is there any other way I could create a parameter query for the user to enter a minimum and maximum age using the D O B field?

Charlotte

 

[Chrafangau]

Are you putting the 'Between x AND y' part in the criteria row? Is it possible to get either a screenshot or a copy of the query's SQL?

 

[claine]

Hi Chrafangau

I have used the "Between [Minimum Age:] AND [Maximum Age:]" and it is showing an error 'data type mismatch in criteria expression.

I have copied the query's SQL:
SELECT CLIENT.[Client ID], CLIENT.Title, CLIENT.[Forename(s)], CLIENT.Surname, CLIENT.[D O B], Year(Now())-Year([D O B]) AS Age
FROM CLIENT INNER JOIN SALES ON CLIENT.[Client ID] = SALES.[Client ID]
WHERE (((Year(Now())-Year([D O B])) Between [Minimum Age:] And [Maximum Age:]));

Charlotte

 

[namliam]

FYI: Year(now()) - Year(DOB) can return false information

I.e.
anyone born in 1972 will return 41
while anyone born 1-sept-1972 actually is 41 and anyone born 15-Dec-1972 will be 40 for a good while still.

Instead Year( Date() - [DOB]) - 1900
will return the proper 41 and 40 for above situation

I know the construct above using a "popup box" will be used as a string/text rather than a number, and as such can result in strange results...
if you query anything between 1 and 5 it will also show anyone in their 10/20/30/40 years old instead of only showing 1 thru 5 as you want

To resolve this make sure to wrap Int() around your between values:
... Between Int([Minimum Age:]) And [int(Maximum Age:])

That should resolve most issues but can still throw issues if someone puts in "Ancient" instead of 100 or something idiot like that

 

[claine]

Thank you namliam

I have tested this and I am still getting problems so I have tried something else.

I have now calculated the age in my client form, which works fine. Is there anyway I can add the unbound textbox I created to calculate the age in the form to my Client table? What I am trying to achieve here is to use the Client table in a query after, which has the new information I have created in the form, now in the table.

Charlotte

 

[namliam]

Age is a calculatable value, these type of values SHOULD NEVER be stored in any table

 

[Brianwarnock]

The correct way to calculate age is to check for if a birthday has passed and correct the year calculation, I use Datediff not the twoyear functions and Date() rather than Now() as I am not interested in the time element

DateDiff("yyyy",[dob],date())+(Format([dob],"mm/dd")>Format(date(),"mm/dd")

The parameter format can be forced using the Parameters keyword prior to the Select in SQL

Parameters [min age] integer, [max age] integer

or by selecting query from the menu bar in design grid and parameters from the drop down.

Namliam is correct never store age as it is a calculated field, and one that changes daily.

Brian

 

[claine]

Thank you namliam.

Hi Brianwarnock

I have a D O B field and created an Age field using: Age: DateDiff("yyyy",[D O B],Now())+Int(Format(Now(),"mmdd")<Format([D O B],"mmdd"))

Now I have the age field calculated, I use the criteria: Between [Min age] And [Max age] but when I run the query, I have an error 'data type mismatch in criteria expression.

Charlotte

 

[Brianwarnock]

Mine works no problem, did you use the Parameters key word?
Your code works on my simple test DB

I see that you are still using Now(), it should not stop things working but why?

Brian

 

[claine]

Thank you Brainwarnock

I didn't realise you should never use Now(). I have taken this out now and use the code DateDiff("yyyy",[dob],date())+(Format([dob],"mm/dd")>Format(date(),"mm/dd").

In the criteria I have typed Between [Min Age] And [Max Age] and formatted the parameter to Integer. When I run the query, it works for a few seconds then the 'mismatch in criteria expression' dialog box appears then my results change to #Name?

Charlotte

 

[claine]

Hi Brainwarnock

I have notice that when I change the select in SQL to Parameters [min age] integer, [max age] integer, when I save and re-open, it changes to:

Parameters [min age] Short, [max age] Short

or

Parameters [min age] Long, [max age] Long

Is this something that can be fixed

Charlotte

 

[Brianwarnock]

You can use Now() it is just that if you do not require the time it is more logical to use Date()

You don't need to worry about integer being changed to short or long , they stand for short integer or long integer .

I don't understand your situation but wonder if you have any Null dates

Brian

 

[claine]

Hi Brainwarnock

I checked for dates that were not entered, and there were a couple so once I put in the dates, the parameter worked.

Thank you for your help in solving my problem.

Also thank you to namliam.

Charlotte