Issues with Reminders Query

[DJDJDJDJ]

Hi,



I have a small database to keep track of upcoming events. These events include recurring events such as birthdays etc. as well as one-time events such as passport renewal date. All these events are treated the same.

Table is similar to below:

RecID = Record ID, Auto number
Event = Event Description, Text
EventDate = Date of the event, could be in the past (birthday) or in future, Date Type
ReminderDays = Number of days to be reminded of in advance.

I have a query that looks at the upcoming events. It does not check for a fixed period, such as events in the next 2 weeks. Instead it checked to see if an event is coming up from today up until [ReminderDays]. This way each record can have its own reminder period.

For example:
1, "John BDay", 1-JAN-1970, 14
2. "Passport Renewal", 15-JAN-2012, 30
3. "Milly BDay", 2-JAN-1975, 5

If today's date is 20-DEC-2011, then I will only see the first two records in the result-set.

In my query, I am also calculating Age field by using a function.

Query:

 
SELECT nextdate-date() AS [Days Until Event], *
FROM (SELECT age([EventDate])+1 AS Age, dateadd("yyyy",Age,
 
[EventDate]) AS NextDate, * FROM tblReminders WHERE (((Int(Format
(DateAdd("d",-1,Date()),"yyyy\.mmdd")-Format
([EventDate],"yyyy\.mmdd")))<Int(Format(DateAdd("d",
 
[ReminderDays],Date()),"yyyy\.mmdd")-Format
([EventDate],"yyyy\.mmdd")))) And removed=0 ) AS [%$##@_Alias]
 
ORDER BY nextdate;

Public Function Age(dteDOB As Date, Optional SpecDate As Variant) As Integer
Dim dteBase As Date, intCurrent As Date, intEstAge As Integer
If IsMissing(SpecDate) Then
dteBase = Date
Else
dteBase = SpecDate
End If
intEstAge = DateDiff("yyyy", dteDOB, dteBase)
intCurrent = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
Age = intEstAge + (dteBase < intCurrent)
End Function

All seems to work except that if I have a future event that is not due for several years, it still shows in my list. Using the example above, I should not see the following record in my resultset.

4. "Driving License Renewal", 5-JAN-2015, 30

Any help is appreciated.

Thanks.

[DJDJDJDJ]

help, please?

[orange]

What exactly is your question/issue?
What would you like your set up to do?

What exactly is your table definition?

[DJDJDJDJ]

What exactly is your question/issue?
What would you like your set up to do?

What exactly is your table definition?

Hello Orange,

Here are answers to your question.

I do not want to include any future events unless their due-date ([Event Date]) is between today and certain number of days ([ReminderDays]).

Tables structure is given in my original post above. In the structure listing above, I am showing 4 fields. There are other fields in the table but they are irrelevant to this particular question.

Thanks.

[orange]

Well the field "removed" is relevant since it is prompted for in your query.

[DJDJDJDJ]

Well the field "removed" is relevant since it is prompted for in your query.

Sorry, forgot to mention that field. Removed is a Y/N field to permanenty removing a listing from any of the results without actually deleting it from the DB.

Thanks.