Alter part of a Union query 1

[THWatson]

Using Access 2003(2000 format)

The Union query below provides the record source for a report that shows Celebrations (Birthdays, Wedding Anniversaries, and Kiwanis Anniversaries) for a month selected on a form.

What I want to do is change the piece of code in blue so that it calculates the # of Wedding Anniversary years as of the Wedding Anniversary day regardless of when in the month the report is printed.

The club secretary prints a report for the President early in the month. If the Wedding Anniversary date is later in the month than the date the report is printed, the # of years is one year less than it should reflect. (e.g. if a member's wedding anniversary date is July 28, 1980 and the report is printed today the # of years shown will be 28 whereas I want it to show 29).

SELECT MemberID, LastName,PreferredName, DateOfBirth as TheDate, "Birthday" as DateType,Status,DateDiff("yyyy",[DateOfBirth],Date())+Int(Format(Date(),"mmdd")<Format([DateOfBirth],"mmdd")) As FixYears,Month(TheDate) As Month,Day([DateOfBirth]) as Day,Switch([Month]=1,"January",[Month]=2,"February",[Month]=3,"March",[Month]=4,"April",[Month]=5,"May",[Month]=6,"June",[Month]=7,"July",[Month]=8,"August",[Month]=9,"September",[Month]=10,"October",[Month]=11,"November",[Month]=12,"December") AS Sort
FROM tblMembers
WHERE (tblMembers.Status="Active" Or tblMembers.Status="Senior") And ([DateOfBirth] Is Not Null) 
UNION ALL
SELECT MemberID, LastName, PreferredName, WeddingAnniversary, "Wedding Anniversary",Status,[COLOR=blue]DateDiff("yyyy",[WeddingAnniversary],Date())+Int(Format(Date(),"mmdd")<Format([WeddingAnniversary],"mmdd")) AS FixWeddingAnniversaryYears,Month(WeddingAnniversary) As Month,Day([WeddingAnniversary]) as Day[/color],Switch([Month]=1,"January",[Month]=2,"February",[Month]=3,"March",[Month]=4,"April",[Month]=5,"May",[Month]=6,"June",[Month]=7,"July",[Month]=8,"August",[Month]=9,"September",[Month]=10,"October",[Month]=11,"November",[Month]=12,"December") AS Sort
FROM tblMembers
WHERE (tblMembers.Status="Active" Or tblMembers.Status="Senior") And ([WeddingAnniversary] Is Not Null) 
UNION ALL SELECT MemberID, LastName, PreferredName, YearJoined, "Kiwanis Anniversary",Status,DateDiff("yyyy",[YearJoined],DateSerial(Year(Date()),9,30)) AS Sept30KiwanisAnniversary,Month(YearJoined) as Month,Day([YearJoined]) as Day,Switch([Month]=1,"January",[Month]=2,"February",[Month]=3,"March",[Month]=4,"April",[Month]=5,"May",[Month]=6,"June",[Month]=7,"July",[Month]=8,"August",[Month]=9,"September",[Month]=10,"October",[Month]=11,"November",[Month]=12,"December") AS Sort
FROM tblMembers
WHERE (tblMembers.Status="Active" Or tblMembers.Status="Senior") And ([YearJoined] Is Not Null)
ORDER BY Month, Day, DateType, LastName, PreferredName;

Thanks.

Tom

 

[PHV]

simply replace this:
(),"mmdd")<Format
with this:
(),"mmdd")<[!]=[/!]Format

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

PHV
If I understand you correctly, a piece of thee code would now read

DateDiff("yyyy",[WeddingAnniversary],Date())+Int(Format(Date(),"mmdd")[b]<=[/b]Format([WeddingAnniversary],"mmdd")) AS FixWeddingAnniversaryYears,Month(WeddingAnniversary) As Month,Day([WeddingAnniversary]) as Day

However this does not quite do it. For example, one member's Wedding Anniversary date is 7/25/1975. If the report is printed "anytime" in July the # of years should show as 34, but using "<=" gives a result of 33 if printed today.

It appears to me that maybe (),"mmdd")=Format works properly.

That seem right to you?

Tom

 

[PHV]

OOps, sorry.

DateDiff("yyyy",[WeddingAnniversary],Date())+Int(Month(Date())<Month([WeddingAnniversary])) AS FixWeddingAnniversaryYears

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

PHV
Yep. Thanks.

Tom