Art-
SELECT Count(tblApplicant.ApplicantID) AS CountApplicantsOver55
FROM tblApplicant
WHERE (((CalcAge([DOB]))>=55));
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
------------------------------------
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Tuesday, April 03, 2012 10:31 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Return Count of Records based on Age in Query
Age Function works as you shown me but I need to just count the records where the applicant is 55 years or older.
I tried this but it did not work.
SELECT Count(tblApplicant.ApplicantID) AS CountOfApplicantID, CalcAge([DOB]) AS Age
FROM tblApplicant
HAVING (((CalcAge([DOB]))>=55));
With warm regards,
Arthur Lorenzini
Sioux Falls, South Dakota
________________________________
From: Bill Mosca <wrmosca@comcast.net>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, April 3, 2012 3:25 PM
Subject: [MS_AccessPros] Re: Return Count of Records based on Age in Query
Art -
All you need is a Age function.
Public Function CalcAge(DOB As Variant)
'Purpose : Calculate age
'DateTime : 3/18/2000 10:50
'Author : Bill Mosca
On Error Resume Next
CalcAge = DateDiff("yyyy", DOB, Now()) _
+ Int(Format(Now(), "mmdd") < Format(DOB, "mmdd"))
End Function
Then use it in your query like this:
SELECT FirstName, LastName, DOB, CalcAge([DOB]) as Age
FROM MyTable
--- In MS_Access_Professionals@yahoogroups.com, "Art" <dbalorenzini@...> wrote:
>
> I need to get a count of records for all applicants 55 years of age or older.
>
> Fields
>
> ApplicantID
> DOB
>
> Please advise
>
> Thank you
>
> Art Lorenzini
> Aioux Falls.
>
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar