By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,650 Members | 753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,650 IT Pros & Developers. It's quick & easy.

Oldest:Max (DateDiff('m', Date)/12) Youngest:Min (DateDiff('m',Date)/12)

P: 1
I'm new to Access so please be kind...I'm trying to determine oldest and youngest employee, I'm sure that the line below is copied verbatim, any ideas I would be most grateful, and so would my shrink.
Thank you
Oldest:Max (DateDiff('m', Date)/12) Youngest:Min (DateDiff('m',Date)/12)
May 5 '07 #1
Share this Question
Share on Google+
2 Replies


JConsulting
Expert 100+
P: 603
[code]
SELECT distinct (SELECT fullname from tbltest a where date2 = (select Max([date2]) from tbltest)) AS oldest, (SELECT fullname from tbltest a where date2 = (select Min([date2]) from tbltest)) AS youngest
FROM tbltest;
[/code/]
May 6 '07 #2

ADezii
Expert 5K+
P: 8,628
I'm new to Access so please be kind...I'm trying to determine oldest and youngest employee, I'm sure that the line below is copied verbatim, any ideas I would be most grateful, and so would my shrink.
Thank you
Oldest:Max (DateDiff('m', Date)/12) Youngest:Min (DateDiff('m',Date)/12)
Here is your answer from a code perspective. I'm sure the dreaded 'SQL Gang' led by Mary and NeoPa will soon come on the scene and have a more practical answer. I'm just happening to experience a SQL meltdown, sorry:
Expand|Select|Wrap|Line Numbers
  1. Dim dteYoungestEmployee As Date
  2. Dim dteOldestEmployee As Date
  3.  
  4. dteYoungestEmployee = DLookup("Max([BirthDate])", "tblEmployee")
  5. dteOldestEmployee = DLookup("Min([BirthDate])", "tblEmployee")
  6.  
  7. Debug.Print "The youngest Employee was born on " & dteYoungestEmployee & _
  8.             " and is " & DateDiff("yyyy", dteYoungestEmployee, Now()) & _
  9.             " years old."
  10.  
  11. Debug.Print "The oldest Employee was born on " & dteOldestEmployee & _
  12.             " and is " & DateDiff("yyyy", dteOldestEmployee, Now()) & _
  13.             " years old."
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. The youngest Employee was born on 1/6/1992 and is 15 years old.
  2. The oldest Employee was born on 6/1/1942 and is 65 years old.
May 6 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.