Hi Art,
instead of HAVING, which is applied to the aggregate results, use WHERE to limit records before they are counted ... and delete the extra parentheses :)
HAVING ( CalcAge([DOB]) >= 55 );
Warm Regards,
Crystal
Access Basics by Crystal (Bill Mosca's site)
http://thatlldoit.com
Free 100-page book that covers essentials in Access
*
(: have an awesome day :)
*
________________________________
From: Art Lorenzini <dbalorenzini@yahoo.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, April 3, 2012 2:31 PM
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]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar