Calculating Function that limits Data Records

[charles woods via AccessMonster.com]

I am trying to qualify people by age. The data available is a date of Birth
and a arrival time in 2 different tables. Calculating the age is not a
problem.
The tables are linked tables through ODBC with an Oracle DB. I have a
function built to calculate the AGE, but when I try to limit this in the
criteria (eg. >21) I receive a ODBC connection error.
No problem. I will just build the selectivity within the AGE function. But
how! recordset.MoveNext?
What bit of code will enable me to calculate an Age and then if the Age is
<22, drop the recordset and move to the next?

Thanks for any help you may offer!

 

[Guest]

Hi, Charles.

when I try to limit this in the
criteria (eg. >21) I receive a ODBC connection error.

It's probably a syntax error.

No problem. I will just build the selectivity within the AGE function. But
how! recordset.MoveNext?
What bit of code will enable me to calculate an Age and then if the Age is
<22, drop the recordset and move to the next?

That would be one of the most difficult ways that I can think of to
accomplish the task. Since you've already successfully linked the tables to
Oracle (that's the hard part), the easiest way to reduce "selectivity" of the
data set is to use a query with a join on the two tables, then use the > 21
criteria in the WHERE clause. This will return a data set with only the
records you need.

For example, if the following two tables were used:

Table name: tblPersons
Field name: PersID, AutoNumber (Primary key)
Field name: DOB, Date/Time
Field name: LName, Text
Field name: FName, Text

Table name: tblArrivals
Field name: ID, AutoNumber (Primary key)
Field name: PersID, Number (Foreign key)
Field name: Arr, Date/Time

.... where DOB is the date of birth, Arr is the date of arrival, LName is the
person's last name, and FName is the person's first name. If you need all of
the records where the person was at least 22 years old on the date of
arrival, then the following SQL statement would return these records and show
the person's name and the primary key of that record:

SELECT tblPersons.PersID, FName, LName
FROM tblPersons INNER JOIN tblArrivals ON tblPersons.PersID =
tblArrivals.PersID
WHERE ((DateDiff("yyyy", DOB, Arr) + Int(Format(Arr, "mmdd") < Format(DOB,
"mmdd"))) > 21);


HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.