Incorrect query results when calling for report

Topic

New Reply

Using Access 2007

A members’ database. On a form the user selects the month for which a report is desired. This calls for a report of members’ wedding anniversaries occurring in the month selected from the list on the form.

Code:

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)

If the user selects March, and we're in March, the results are correct.

If, however, the user selects March from sometime in February, the results in anniversary years are 1 less than they should be.

e.g. Member Brown has actually been married 30 years as of March 15. If we are in March, 30 years is what shows when the March report is selected.

However, if the user wants to run the report in late February, Member Brown shows as being married 29 years in March.

Not exactly sure how to fix this. I suspect it has to do with the Date() function.

Suggestions would be appreciated.

Tom

Reply | Quote

Replies

to determine whether or not the day in the year is before the birthday or not.

eg
Function AgeCalc(ByVal pvarDate As Variant) As Variant
‘–Calculates the number of years of age from passed variable with reference to today
‘–Input pvarDate contains date
‘–Output Age expressed as an integer
‘–Example: Age = fnAgeCalc(CVDate(“21/04/1967”))

On Error Resume Next

If VarType(pvarDate) = 7 Then
AgeCalc = DateDiff(“yyyy”, pvarDate, Date) + (DateValue(Format(pvarDate, “dd/mm”)) > Date)
If AgeCalc > 120 Then
AgeCalc = Null
End If
Else
AgeCalc = Null
End If

End Function

Reply | Quote

Hi Tom,

You can simplify the “Sort” field, where you have used the Switch function to print the name of the month by using the built-in MonthName function. For example, in query design view, enter the following expression in the Field row:

Sort: MonthName(Month([WeddingAnniversary]))

Notes:
I would think that you’d want a numeric field as a sort result, ie: Sort: Month([WeddingAnniversary])
I would avoid aliasing any columns with reserved words. Both Month and Day are considered reserved words in Access.

If, however, the user selects March from sometime in February, the results in anniversary years are 1 less than they should be.

It appears as if you want to round the result up to the nearest whole number, in years. So, should 9.5 years round up to 10 but 9.49 years round down to 9 years? If so, I think the following expression should do the trick:

WedAnnvYears: Round((DateDiff(“d”,[WeddingAnniversary],Now())+Int(Format(Now(),”mmdd”)<Format([WeddingAnniversary],"mmdd")))/364.25+0.000001,0)

In the above expression, I'm calculating an age in days, and then converting to years. Finally, I add a very small number, 0.000001 to the result and apply the Round function, rounding to zero decimal places.

Tom Wickerath
MS Access MVP
2006 – 2011

Reply | Quote

The critical expression is this one:

Code:

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

By using the Date function in here, the calculations are based on the day you run the report, rather than the Date selected from the form.
Does the form used to select a Month allow a Date to be selected? If not what date in the month should be used?

So I think each of the Date() expressions here needs to be replaced with a reference to the date on the form you are using.

Reply | Quote

Tom Wickerath’s “rounding” of the Wedding Anniversary date 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.

Code:

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

Reply | Quote

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

I previously suggested you need to replace the Date() function with references to the date you want things calculated from.

Reply | Quote

Hi John
Right you are.

I’m still working on that aspect. The user goes to an unbound form that only has two List Boxes. One is a List Box which is a Value List of months January through December. If the user, however, wishes to choose a range of Months, 2 list boxes appear, each having the months January through December in them.

I guess the best thing, probably would be, to use a DateSerial function to use the last day of the desired month, or months.

Tom

Reply | Quote