Multiple Criteria Date Query

[burrina]

I am down to the point where I need to filter the data. I will try and explain as best as I can.My database is a Check Register db with scheduled transactions.


I have everything in one table for this. Table name is tblEvent. The start of my criteria this; I think?? I can do this individually but NOT all together as one query and then display on one form, which is what I need.

To be clear, I need to see what events are due for each day.If they are scheduled daily,weekly,monthly,quarterly or yearly. I am shooting for one query to accomplish this or at least get it all on one form. I have attached 2 tables with data in them for demo purposes.



If ([EventStart]) <= Date() and ([Scheduled]) = False 'Have NOT Been Scheduled.

1. Now, I need to see all the records that are due by day, i.e. "d" with a EventStart <= Date() and Scheduled = False and either a "o" or ( # ) of times of ReCurCount
2. Next the same for month, i.e. "m" where EventDate >30

EventStart First Occurence of event.
RecurCount How many times the event will recur. 0 for one-off event.Leave blank if open-ended.
PeriodFreq How may days/months/years between recurrences.
PeriodTypeID Type of period for recurrence (day,month,year,...)
Scheduled Yes/No Has Event Been Successfully Scheduled.


PeriodTypeID = d day(s)
= ww week(s)
= m month(s)
= yyyy year(s)


EventID Primary Key
EvenStart Date
RecurCount Number
PeriodFreq Number
PeriodTypeID Text
Scheduled Yes/No

txtEndDate =IIf(([PeriodFreq] Is Null) Or ([PeriodTypeID] Is Null) Or ([RecurCount] Is Null),Null,DateAdd([PeriodTypeID],[RecurCount]*[PeriodFreq],[EventStart]))
__________________________________________________ __________________________________________________ ___________________________________
Here is what I have come up with, brain is fried, can't think!
(txtentered) is the ScheduledDate (EventStart) is the StartDate (txtEndDate) is the StopDate

(RecurCount) How many times the event will recur. 0 for one-off event. Leave blank if open-ended.
(PeriodFreq) How many days/months/years between recurrences. Required.
(PeriodTypeID) Type of period for recurrence (day, month, year, ...) Required.

'---------------------------------------------------------------------------------------
' Procedure : Form_Current
' Author : Dave
' Date : 12/31/2012
' Purpose : Check For Required Criteria Before Allowing Event To Be Entered.
'---------------------------------------------------------------------------------------
'

If ([txtentered]) <= Date() and ([RecurCount]) ="0" and ([PeriodFreq]) ="d" Then 'Open-Ended Event
Me.Scheduled = True
ElseIf ([txtentered]) <= Date() and ([PeriodFreq]) ="d" and ([RecurCount]) >0 Then 'Daily Reccurring Event
Me.Scheduled = True
End If

If Date() > ([txtEndDate]) Then 'Event Time Has Passed and Event has Expired.
Me.Scheduled = False
Me.txtentered = Null
End If


This is just too complex for me, Thanks anyway.