Need Advice on Date Programming Code

[rgomez1999]

Need help trying to figure the best way to solve a date programming feature in my database. (Using Access 2000)

I have three fields on my form Date Enrolled, FirstReport & DueDate. Upon the Date Enrolled I need to do a report every 3 months for 1 year. (DueDate is the actual field that updates itself according to the PC's Calendar)

However here's the catch, after 1 year from the last ReportsDueAfter I must now do a report every six months.

I already have a module that performs a query that handles this function for me every three months I would like to implement a sample statement like:

Last ReportDueDate was the 4th Quarter of the 1st Year now add every six months to the ReportDueDate field. If this is possible I would be grateful for anyone's help. This has been a long term battle for me.

Listed below are codes I already have in place for every 3 months.

EnrollDate Field (After Update Event)
=====================================
If EnrollDate > #12/31/1999# Then
FirstReport = DateAdd("m", 3, [EnrollDate])
End If

Me![DueDate] = ComputeDate(Me![FirstReport])
ComputeDate2 (Me![FirstReport])
ShowWarning
Repaint
End Sub

=========================================
On The Form Properties (On Current Event)
=========================================
Private Sub Form_Current()
Combo104 = StudentID 'Update the Find A Record combo box.
ComputeDate2 (Me![FirstReportDate])
ShowWarning
End Sub
==========================
Module 1 - Print On Report
==========================
Function PrintOnReport(FP As Date, FromDate As Date, ToDate As Date) As Boolean
Dim HoldDate As Date

HoldDate = FP
If HoldDate >= FromDate And HoldDate <= ToDate Then
PrintOnReport = True
Exit Function
End If

Do Until HoldDate > ToDate Or (HoldDate >= FromDate And HoldDate <= ToDate)
HoldDate = DateAdd(&quot;m&quot;, 3, [HoldDate])
Loop
If HoldDate >= FromDate And HoldDate <= ToDate Then
PrintOnReport = True
Exit Function
End If
PrintOnReport = False
End Function

==================================
Module 2 - ComputeDate
==================================
Function ComputeDate(FP As Date) As Date
Dim HoldDate As Variant

If FP < Date Then
HoldDate = FP
Do Until HoldDate >= Date
HoldDate = DateAdd(&quot;m&quot;, 3, [HoldDate])
Loop
ComputeDate = CDate(HoldDate)
Else
ComputeDate = CDate(FP)
End If

End Function

=====================================
Module for the Query1
=====================================
Function GetMonths()
Dim varItem As Variant
Dim strList As Variant
Const KeepGoing = &quot; OR &quot;

With Forms![Frm_GetDates]![GetMonths]
If .MultiSelect = 0 Then

Else
For Each varItem In .ItemsSelected
strList = strList & .Column(1, varItem) & KeepGoing
Next varItem

End If
End With
strList = Left(strList, Len(strList) - 4)
Stop
End Function

=========================================
Query1 (Is an update query)
=========================================
UPDATE tblMain SET tblMain.DueDate = IIf(IsNull([tblMain].[DueDate]),DateAdd(&quot;m&quot;,3,[FirstReportDate]),IIf([tblMain].[DueDate]<Date(),DateAdd(&quot;m&quot;,3,[DueDate]),[DueDate]))
WHERE ((([tblMain].[DueDate]) Is Null Or ([tblMain].[DueDate])<Date()));


=============================================
The Select Query for the Get Due Dates Report
=============================================
SELECT TblMain.StudentID, TblMain.LastName, TblMain.FirstName, TblMain.Counselor, TblMain.EnrollDate, TblMain.FirstReportDate, ComputeDate([FirstReportDate]) AS YNextDue, PrintOnReport([FirstReportDate],[Forms]![Frm_GetDates]![From],[Forms]![Frm_GetDates]![To]) AS X, DateAdd(&quot;m&quot;,-3,[yNextDue]) AS CurrentDue
FROM TblMain
WHERE (((PrintOnReport([FirstReportDate],[Forms]![Frm_GetDates]![From],[Forms]![Frm_GetDates]![To]))=True) AND ((TblMain.ActiveClient)=True));



I hope this helps you understand what I'm trying to accomplish. Thank you all.

 

[MichaelRed]

I think that I would create a seperate table of reports. On each occassion of a report, enter it in the table. This (Table) probably has the StidentId and ReportDate.

ON a &quot;Daily&quot; basis run queries to determine if, for each studentID a report is due. The First query would get all of the students with three or fewer reports. If the most recent report is more than 3 m (or 1 q) past, you need a report.

A second query would get all students w/ four or more reports. If the most recent is more than 6 m (or 2 q) past, you need a new report.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over