Help on flagging up records within a time period?

[pablavo]

Hi All

I've been looking at a couple of things that can help me and can't find anything solid. I hope some of you can help or at least point me in the right direction.

One of the forms I'm working on has dates that reports are due to sent in to the Organisation. the dates are usually worked out up to 4 months in advance.

I'm trying to find a way that will flag up records 4 weeks prior to the data within the "Reports Due" field. Perhaps at the start of the month before there due.

Ideally I was looking for a way that would inform an administrator via email but I don't know if that's possible. The idea would be that the admin doesn't have to locate the record to find out within the database. Alternativly a report would be good that lists the record dates 4 weeks before.

Does anyone know of a good solution to this one? I have really tried to find one but with no avail!

Thanks alot for any help!!

 

[Dennisk]

use a query where the criteria subtracts 4 weeks from the current date and tests that aginst the dates in your table.

 

[pablavo]

Thanks Dennisk for the reply.

I would like to ask a question. would I create the query and subtract 4 weeks from the date and put this field into a report? I guess this would be the easiest way to view the records. I'm unsure of how I would use criteria.

thanks

 

[Dennisk]

Pablavo

there are two ways to do this
either add say 4 weeks to the date in question and test if it is less than today or

subtract 4 weeks from the current date

both can use either the DateAdd Function or you can add or subtract the number of days directly.

Here is an example that retreives all patients whose Rederral date is 28 days or greater in the past.

SELECT tblClient.ClientID, tblClient.NHSNumber, tblClient.Surname, tblClient.Forename, tblReferral.ReferralDate, [referraldate]+28 AS TriggerDate
FROM tblClient INNER JOIN tblReferral ON tblClient.ClientID = tblReferral.ClientID
WHERE ((([referraldate]+28)<=Date()));

 

[pablavo]

Thanks Dennisk for your help, I'll have a look at DateAdd and see if that makes things easier!