Here is the entire SQL statement:
SELECT Membership.BirthDt, IIf([BirthDtCd]>1,Format([BirthDt],"mmmm"),"") AS
Mo, IIf([BirthDtCd]=3,DatePart("d",[BirthDt]),"") AS Dt,
DatePart("yyyy",[BirthDt]) AS Yr, IIf([BirthDtCd]=1,0,1) AS SortCd,
Membership.BirthDtCd, Membership.ChapID, Chapters.ChapName,
Chapters.ChapCity, Chapters.ChapDistrict, Membership.MbrID, Membership.Title,
Membership.LN, Membership.FN, Membership.MaidenName, Membership.Addr1,
Membership.Addr2, Membership.City, Membership.State, Membership.Zip,
Membership.MbrStatus, Membership.HusbName,
(Year(Date())-Year(Membership.BirthDt)) AS YrLapse,
(Year(Date())-Year(Membership.BirthDt)) AS Expr1
FROM Chapters INNER JOIN Membership ON Chapters.ChapID = Membership.ChapID
WHERE (((Membership.BirthDt) Is Not Null And (Membership.BirthDt) Not Like
"") AND ((Membership.MbrStatus) Like "A") AND
(((Year(Date())-Year([Membership].[BirthDt])))>=[Minimum Age Parameter]))
ORDER BY Membership.LN, Membership.FN;
Jerry Whittle said:
I have no trouble getting it to work with Access 2007 and dates over 100 years.
Please provide the entire SQL statement. Open the query in design view. Next
go to View, SQL View and copy and past it here.
If the SQL statement doesn't start with the word PARAMETER, I bet that is
the problem. You need to define a date parameter as Date/Time or things can
get strange.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
I have a query to extract all persons with an age eaual to or greater than a
user-entered paramater (criteria). The expression
(Year(Date())-Year([Membership].[BirthDt])) when compared to the criterion
does not extract any age 100 or older. In addition, it extracted a person
with an erroneous future birth date of 2968. I had the same erroneous
results using DateDiff().
I will appreciate any help to correct the query to extract even those with
ages 100 or greater and to exclude any future dates.