Date Alert not showing any results

[magster06]

Hi all,

Can anyone see an error in my query?

I am trying to setup a date alert that will notify me when a doctors excuse is 2 weeks from expiration.

SELECT LightDuty.[Last Name], LightDuty.[Permanent Assignment], LightDuty.Unit, LightDuty.[Doctors Excuse Exp], LightDuty.EmailAddress, LightDuty.[Email Sent]
FROM LightDuty
WHERE (((LightDuty.[Doctors Excuse Exp])<=(Date()-14)) AND ((LightDuty.[Email Sent])=False));

Thanks ahead of time!

 

[pr2-eugin]

Can you replace the WHERE you currently have with this one?

WHERE ((DateDiff("d",Date(),LightDuty.[Doctors Excuse Exp])<=14) AND ((LightDuty.[Email Sent])=False));

Also, make sure that the DataType of Doctors Excuse Exp is Date.

 

[pr2-eugin]

Wait a second.. Could you explain your requirement again?? I think I got it wrong.. Give a sample data if you could..

 

[RainLover]

Do you get an error message.

What type of field is Email Sent. Date Text or Yes/No or something else.

Does it work if you leve this out <<AND ((LightDuty.[Email Sent])=False));>>

 

[magster06]

pr2-eugin,

I am trying to have a query (which will populate a form) to let me know when someones doctors excuse will expire 14 days before the actual expiration date.

eg.. if the expiration date is 11/01/2012, then I want an alert to let me know that this doctors excuse will expire 14 days before 11/01/2012

 

[RainLover]

Sorry Paul,

You posted while I was typing.

I will leave this to you.

 

[magster06]

The "Email Sent" is yes/no and the query still shows no results or errors when I remove this.

The query also did not show any results when the "Email Sent" is in (or errors)

 

[magster06]

pr2-eugin,

Ok, it is working now. When I looked over the change that you wanted me to make, I noticed that I left the minus in front of the 14 and you did not include it.

When I removed this, then the query now works!

Thanks so very much for your help!


Just curious as to why the first expression did not work?

 

[pr2-eugin]

Not sure if I was of any help there.. but glad you have it sorted..

@RainLover : It is okay.. I am happy to share.. after all, this is an open Forum and you are much more experienced that I am..