Time is running out!

[kybb1]

Hello,

I have a table that stores a DueDate (date field) and a FollowUpDays (number field).


What I'm trying to accomplish is create a report based on projects where DueDates are within 5 days of being due.

I'm not sure how to set the query which the report will be based on.

For instance:
DueDate=8/15 and FollowUpDays=30
On July 21st that record should be included in the query.

Can someone please help me.

Thanks.

 

[fuzzygeek]

"What I'm trying to accomplish is create a report based on projects where DueDates are within 5 days of being due. "

does not correlate with your example

"For instance:
DueDate=8/15 and FollowUpDays=30
On July 21st that record should be included in the query."

which is 25 days from the Due Date. Anyway a sql entry that would do the first (5 days) is similiar to the following.

SELECT YourTableName.DueDate, DateDiff("y",date, [DueDate]) AS MyDateDiff
FROM YourTableName
WHERE (((DateDiff("y",Date,[DateDue])) Between 0 And 5));

Put MyDateDiff: DateDiff("y", Date, [DateDue]) as the field name and Between 0 And 5 on the Criteria line

 

[kybb1]

Thanks....but...

Sorry for the confusion....I will try to be more clear.

The FollowUpDays field is filled in by the user and is determined by the user. Therefore this field may have 30, 45, 60 as values. 5 Days after each one of the values (30, 45, 60) a reminder is sent to the respective person.

In other words, if the project needs to be followed up on by 8/15 and the FollowUpDays = 30 on the 5th day of the FollowUpDays (25 days BEFORE DueDate) he/she will get a notification via email which in the case I used would be 7/21. Better said I guess is 5 days into the FollowUpDays should trigger a reminder....In other words 2 things have to be monitored, the DueDate and the FollowUpDays. FollowUpDays is the number (less 5) of days PRIOR to the DueDate that a reminder notification must be sent.

I have most of this all worked out....just not the proper syntax for the query.

I've tried your suggestion...but I get an error for "date" as though it's a parameter.

Again sorry for the confusion and thanks for the help.

 

[fuzzygeek]

Oops

Use Date() instead of just Date. I can't work on the rest right now, but this should give you enough of an idea to approach it. Use an IIf function to determine the Followupdays, 5 day and DueDate. Something like Date() - [Followupdays] + 5 instead of just Date() in the DateDiff function.

Date() is a function that returns the current date.

 

[Travis]

Try this:

SELECT DateDue, FollowUpDays, DateAdd("d",-([FollowUpDays]-5),[DateDue]) AS StartFollowupDate
FROM [YourTableName]
WHERE ((([DateDue])>=Date()) AND ((DateAdd("d",-([FollowUpDays]-5),[DateDue]))<=Date()));

 

[fuzzygeek]

The following sql will give you a five day period for generating the reminder, however if you want it precisely 5 days into the FollowUpDays, then change the between and statement to = 0. This is fine if you have a 24/7 system up, if you do not have the weekends for the system to generate the reminder, then you have to have a more involved function that takes weekends into account.

SELECT YourTable.DueDate, DateDiff("y",Date(),[DueDate]-[FollowUpDays]+5) AS MyDateDiff
FROM YourTable
WHERE (((DateDiff("y",Date(),[DueDate]-[FollowUpDays]+5)) Between -5 And 0));

 

[kybb1]

Thanks....

Many thanks to all replies...for learning purposes..I tested all and they both work...thanks for your help...never would have gotten this without you guys....

Much appreciated!