Query yields different results depending upon when run

[THWatson]

Using Access 2007

A Kiwanis club database.

On a form, the user selects a month from a list. This generates a report that gives Birthdays, Wedding Anniversaries, and Kiwanis Anniversaries, for the month selected.

Here is the SQL that runs to generate the report.

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,DateDiff("yyyy",[WeddingAnniversary],Date())+Int(Month(Date())<Month([WeddingAnniversary])) AS FixWeddingAnniversaryYears,Month(WeddingAnniversary) As Month,Day([WeddingAnniversary]) 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 ([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;

The club secretary ran the March report on February 28 in order to provide the President with that March report at the meeting on March 3.

Data was correct for Birthdays and Kiwanis Anniversaries. The Wedding Anniversaries generated were one year out.

The correct results for March should have been...
Member X, 39 years
Member Y, 36 years
Member Z, 30 years

The March results, when the report was run on February 28 were
Member X, 38 years
Member Y, 35 years
Member Z, 29 years

Can anyone spot how I need to fix this query, so that the correct results yield for the Month selected, irrespective of when the report is run?

Thanks.

Tom

 

[THWatson]

I should have indicated this is the piece of the query that isn't quite correct...

SELECT MemberID, LastName, PreferredName, WeddingAnniversary, "Wedding Anniversary",Status,DateDiff("yyyy",[WeddingAnniversary],Date())+Int(Month(Date())<Month([WeddingAnniversary])) AS FixWeddingAnniversaryYears,Month(WeddingAnniversary) As Month,Day([WeddingAnniversary]) 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 ([WeddingAnniversary] Is Not Null)

Tom

 

[PHV]

Don't use Date() but the date you want to test against.

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

 

[dhookom]

I don't like the following because you are clearly giving the result of an expression, a name of a function. (Month() and Month)

  Month(WeddingAnniversary) As Month

In addition, you can replace

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")

with

MonthName([Month])

Or, better yet, don't use a derived alias in another expression:

Format([WeddingAnniversary],"mmmm")

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

I have applied a "rounding" of the Wedding Anniversary date which seems to have fixed that part.

I changed "Month" to "TheMonth" and "Day" to "TheDay" in the query so as not to use reserved words.

The entire query is a Union query, which combines Birthdays, Wedding Anniversaries and Kiwanis Anniversaries. I didn't previously post the entire code. Here it is, and things seem to work fine.

If anyone can suggest additional ways to fix something, that would be great.

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(DateOfBirth) As TheMonth,Day([DateOfBirth]) as TheDay,Format(DateOfBirth,"mmmm") 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,Round((DateDiff("d",[WeddingAnniversary],Now())+Int(Format(Now(),"mmdd")<Format([WeddingAnniversary],"mmdd")))/364.25+0.000001,0) AS FixWeddingAnniversaryYears,Month(WeddingAnniversary) As TheMonth,Day([WeddingAnniversary]) as TheDay,Format(WeddingAnniversary,"mmmm") 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 TheMonth,Day([YearJoined]) as TheDay,Format(YearJoined,"mmmm") AS Sort
FROM tblMembers
WHERE (tblMembers.Status="Active" Or tblMembers.Status="Senior") And ([YearJoined] Is Not Null)
ORDER BY TheMonth, TheDay, DateType, LastName, PreferredName;

Thanks!

Tom