Using Access 2003 (2000 format)
I have a report "rptPerfectInRange" that determines the members of a club who have achieved Perfect Attendance within dates selected by the user on a form.
The record source for this report is "qryRangePerfect"
This query pulls from "qryPerfectStreak"
The user selects a Start Date, "txtStartDate", and an End Date, "txtEndDate" on a form.
The parameters in the query are filled from a function
All of this worked fine until I designed an Archive process which archives off Attendance data which is prior to October of 2 years ago. And in fact it still works fine if the dates the user selects is within the time frame from October of 2007 until now.
What I need to be able to do is retrieve the data from both the current data in tblAttendance and the archived data in tblAttendanceArchive.
That, I assume, means modifying the module's function so that the streak calculates on all available data but I have tinkered around with a number of possibilities and can't find the key to it.
Any push in the right direction would be appreciated.
Tom
I have a report "rptPerfectInRange" that determines the members of a club who have achieved Perfect Attendance within dates selected by the user on a form.
The record source for this report is "qryRangePerfect"
Code:
SELECT qryPerfectStreak.MemberID, qryPerfectStreak.FullName, qryPerfectStreak.StreakStartMonth, qryPerfectStreak.StreakLength, qryPerfectStreak.EndMonth, qryPerfectStreak.LastName, qryPerfectStreak.PreferredName, qryPerfectStreak.Status, qryPerfectStreak.LastPerfectAttendance
FROM qryPerfectStreak
WHERE (((qryPerfectStreak.Status)='Active' Or (qryPerfectStreak.Status)='Senior' Or (qryPerfectStreak.Status)='Leave of Absence'))
ORDER BY qryPerfectStreak.LastName, qryPerfectStreak.PreferredName;This query pulls from "qryPerfectStreak"
Code:
SELECT tblMembers.MemberID, tblMembers.LastName, getStreakStart([MemberID]) AS StreakStartMonth, DateDiff("m",[StreakStartMonth],[EndMonth])+1 AS StreakLength, getEndDate() AS EndMonth, [LastName] & ", " & [PreferredName] AS FullName, tblMembers.PreferredName, tblMembers.Status, tblMembers.LastPerfectAttendance
FROM tblMembers
WHERE (((getStreakStart([MemberID]))<=getStartDate()) AND ((tblMembers.Status)="Active" Or (tblMembers.Status)="Senior" Or (tblMembers.Status)="Leave of Absence"))
ORDER BY getStreakStart([MemberID]), tblMembers.LastName, tblMembers.PreferredName;The user selects a Start Date, "txtStartDate", and an End Date, "txtEndDate" on a form.
The parameters in the query are filled from a function
Code:
Option Compare Database
Option Explicit
Public glblEndDate
Public glblStartDate
Public Function getStreakStart(memID As Long) As Date
Dim currentMonth As Date
Dim numMeetings As Integer
Dim numAttended As Integer
Dim numMakeUps As Integer
Dim creditedMeetings
Dim perfectMonth As Boolean
Dim dtmEndDate As Variant
dtmEndDate = getEndDate()
If IsNull(dtmEndDate) Or dtmEndDate = 0 Or Not IsDate(dtmEndDate) Then
dtmEndDate = DateSerial(Year(Date), Month(Date), 1)
End If
currentMonth = DateSerial(Year(dtmEndDate), Month(dtmEndDate), 1)
getStreakStart = currentMonth
perfectMonth = True
Do Until Not (perfectMonth)
perfectMonth = False
numMeetings = getNumberMeetings(currentMonth)
numAttended = getNumberMeetingsAttended(memID, currentMonth)
numMakeUps = getNumberMakeUps(memID, currentMonth)
If numMakeUps > 5 Then
numMakeUps = 4
End If
If numMakeUps + numAttended >= numMeetings Then
perfectMonth = True
getStreakStart = currentMonth
currentMonth = DateSerial(Year(currentMonth), Month(currentMonth) - 1, 1)
End If
Loop
End Function
Public Function getNumberMeetings(dtmDate As Date) As Integer
getNumberMeetings = DLookup("Required", "tblMonths", "MonthYear = " & getSQLDate(dtmDate))
End Function
Public Function getNumberMeetingsAttended(memID As Long, currentMonth As Date) As Integer
Dim monthStart As Date
Dim monthEnd As Date
monthStart = getFirstOfMonth(currentMonth)
monthEnd = getEndOfMonth(currentMonth)
getNumberMeetingsAttended = DCount("MemberID", "qryMeetingsAttended", "MeetingDate >= " & getSQLDate(monthStart) & " AND MeetingDate <= " & getSQLDate(monthEnd) & " AND MemberID = " & memID)
End Function
Public Function getNumberMakeUps(memID As Long, currentMonth As Date) As Integer
Dim monthStart As Date
Dim monthEnd As Date
monthStart = getFirstOfMonth(currentMonth)
monthEnd = getEndOfMonth(currentMonth)
getNumberMakeUps = DCount("MemberID", "qryMakeUps", "MeetingDate >= " & getSQLDate(monthStart) & " AND MeetingDate <= " & getSQLDate(monthEnd) & " AND MemberID = " & memID)
End Function
Function getSQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. allen@allenbrowne.com, June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
getSQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
getSQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function
Public Function getFirstOfMonth(dtmDate As Date) As Date
getFirstOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)
End Function
Public Function getEndOfMonth(dtmDate As Date) As Date
getEndOfMonth = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
End Function
Public Function getMin(mkups As Variant, mkupsallowed As Variant) As Integer
If IsNull(mkups) Then mkups = 0
If mkups > mkupsallowed Then
getMin = mkupsallowed
Else
getMin = mkups
End If
End Function
Public Function getEndDate() As Variant
' you can pull this off a form
'getEndDate = forms("yourForm").yourControlname
getEndDate = Forms("frmPerfectAttendance").txtEndDate
End Function
Public Function getStartDate() As Variant
' you can pull this off a form
'getStartDate = forms("yourForm").yourControlname
'or return a global variable
getStartDate = CDate(Forms("frmPerfectAttendance").txtStartDate)
End FunctionAll of this worked fine until I designed an Archive process which archives off Attendance data which is prior to October of 2 years ago. And in fact it still works fine if the dates the user selects is within the time frame from October of 2007 until now.
What I need to be able to do is retrieve the data from both the current data in tblAttendance and the archived data in tblAttendanceArchive.
That, I assume, means modifying the module's function so that the streak calculates on all available data but I have tinkered around with a number of possibilities and can't find the key to it.
Any push in the right direction would be appreciated.
Tom
