Using dates in a query

[Tom via AccessMonster.com]

Hi,
Im having a hard time making a query. Here is my problem.

A letter is sent to a company each year. Every 3rd year a visit is made to
the company. The year that a visit is made a letter does not need to be sent.
If a company request a visit then a letter does not need to be sent that year.
After a visit, the cycle of letters for two years and then a visit on the
third year resumes, unless they request a visit, in which ofcourse the cycle
would start again.

I would like the query to tell me if the next year requires a visit or a
letter. Im having trouble figuring out what data to track to get the info I
need. Im also having trouble sorting the records for the different years. The
fields that I have tried using are;

LetterDate
VisitDate
TypeOfContact (Letter or Visit)

Thanks

Tom

 

[Guest]

Tom,

If I understand correctly, the cycle is three years based on the interval
since the last visit date. You should be able to determine the kind of
contact based on this:

If Today - VisitDate < 3 Years then Send Letter else Visit

Assuming that the VisitDate contains the date of the last visit. So in years
1 and 2 this would tell you to send a letter; in year 3 it would tell you to
visit. If a visit is requested (is there a field for this or is the visit
date set in advance?), then the difference between Today and VisitDate will
be negative and the cycle would 'reset'.

Try a query like the following:

select Iif ( (DateDiff("yyyy", Date(), [VisitDate]) <> 3) and
(DateDiff("yyyy", Date(), [VisitDate]) > 0), "Letter",
"Visit") as TypeOfContact
FROM [YourTable]

Something like that should get you close. Let us know.

Good Luck!

 

[Tom via AccessMonster.com]

Thanks for helping!

OK, I tried it, and I think we our on the right track, but this is just very
confusing to me. When I ran it I got some results that I dont understand.
If they had a visit in 2004 then they sould get a letter in 2005.
If they had a visit in 2003 then they sould get a letter in 2005.
If they had a visit in 2002 then they sould get a visit in 2005, and a letter
in 2006 and 2007.

The results I got were:
Visit Date 2001 = visit
Visit Date 2002 = visit
Visit Date 2003 = visit
Visit Date 2004 = visit
Visit Date 2005 = visit

It seems that this is very close to working,

Thanks again for your help

Tom

Tom,

If I understand correctly, the cycle is three years based on the interval
since the last visit date. You should be able to determine the kind of
contact based on this:

If Today - VisitDate < 3 Years then Send Letter else Visit

Assuming that the VisitDate contains the date of the last visit. So in years
1 and 2 this would tell you to send a letter; in year 3 it would tell you to
visit. If a visit is requested (is there a field for this or is the visit
date set in advance?), then the difference between Today and VisitDate will
be negative and the cycle would 'reset'.

Try a query like the following:

select Iif ( (DateDiff("yyyy", Date(), [VisitDate]) <> 3) and
(DateDiff("yyyy", Date(), [VisitDate]) > 0), "Letter",
"Visit") as TypeOfContact
FROM [YourTable]

Something like that should get you close. Let us know.

Good Luck!

Hi,
Im having a hard time making a query. Here is my problem.

[quoted text clipped - 18 lines]

Tom

 

[Tom via AccessMonster.com]

Everything seems to work as long as the visit was after 2005. For dates
before 2005, they all get "visit" results because of the >0.
Somehow I need to account for visits made in the past.

Tom

 

[Guest]

Tom,

Change the SQL to look like:

select Iif ( (DateDiff("yyyy", Date(), [VisitDate]) mod 3) = 0, "Visit",
"Letter") as TypeOfContact
from [YourTable];

Cleaner and I don't have any problems running from 2001 through 2008.

Good Luck!