Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Incorrect query results when calling for report

    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

  2. #2
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts

    You need to call a function from your query....

    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

  3. #3
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    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

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by THWatson View Post
    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.
    Regards
    John



  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •