Difficult date query. 1

[BasicBoy]

I need help on this query which at this stage I can only put in words.

The problem is that I have a table named

REMINDERS with a field called

ORIGINALDATE (such as in a date born 10-10-1946 with a recurinterval of 1 - for years - to be shown yearly) or an

ORIGINALDATE (such as in 10-10-2000 with a recurinterval of 2 - for months - to be shown monthly) and a field

RECURINTERVAL (1 for yearly and 2 for monthly)

which must come up yearly and monthly on the specific date - so a birthday of 10-10-1946 must come up on this year on 10-10-2011 and a monthly reminder must come up every month on the 10th.

I need to query the table (named Reminders) with a field (called OriginalDate - in date format) and get a field (called MyReminder - in text format) in Access and get results complying with the following :

Look for an entry in the table REMINDERS which has a ORIGINALDATE of 10-10-1946, check to see how many years have passed since that date (in this case 65) - add 65 years to ORIGINALDATE and see if it exactly matches TODAY.

I have tried the following, but have not been successful :

SELECT * from

REMINDERS

WHERE

IIF(recurinterval=1,Dateadd('yyyy',Datediff('yyyy',Originaldate,TODAY),Originaldate))

IIF(recurinterval=2,Dateadd('m',Datediff('m',Originaldate,TODAY),Originaldate))


= TODAY

Thank you very much for a difficult problem.

 

[PHV]

Something like this ?

SELECT * FROM REMINDERS
WHERE (recurinterval=1 AND Format(Originaldate,'mmdd')=Format(Date(),'mmdd'))
OR (recurinterval=2 AND Day(Originaldate)=Day(Date()))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BasicBoy]

I wish I could give you 2 stars - thanks