2-Month Warning before Expire Dates (1 Viewer)

O

Onehalf03

New member

Local time

Today, 06:20

Joined

Oct 28, 2019

Messages

4

• Nov 11, 2019

• #1

Good Morning,

I am hoping someone can help me. I would like to establish a query that notifies me of upcoming expiration dates 2 months before the true expiration. The formula I have constructed is as shown but it doesn't seem to pull the data and I receive an error message. Notify: IIf([(Expiration Date) Next Review Date]-Date()=80,"Review Needed","")

I appreciate any help I can receive. Thank you!

 

theDBguy

I’m here to help

Staff member

Local time

Today, 06:20

Joined

Oct 29, 2018

Messages

21,449

• Nov 11, 2019

• #2

Hi. Since you're using spaces in your field names, you'll need to enclose them in square brackets.

 

O

Onehalf03

New member

Local time

Today, 06:20

Joined

Oct 28, 2019

Messages

4

• Nov 11, 2019

• #3

I removed the Spaces, but still no luck. Is it the formula I am using? Notify: IIf([(Expiration_Date)_Next_Review_Date]-Date()=60,"Review Needed","")

 

theDBguy

I’m here to help

Staff member

Local time

Today, 06:20

Joined

Oct 29, 2018

Messages

21,449

• Nov 11, 2019

• #4

Hi. Does your field name actually contain parentheses? Right now, your expression is looking for an exact match. Try using <= instead of just = to see if it will make any difference.

 

C

CJ_London

Super Moderator

Staff member

Local time

Today, 14:20

Joined

Feb 19, 2013

Messages

16,606

• Nov 11, 2019

• #5

if expiration date includes a time element then it will never be a whole number so you will never get your formula to =80. Agree with DBG's suggestion - if only because if you don't run the query every day (including weekends) you will miss your 'notification'.

Also agree re using non alpha numeric characters in field names - even with square brackets you will on occasion get a misleading error messages.

 

O

Onehalf03

New member

Local time

Today, 06:20

Joined

Oct 28, 2019

Messages

4

• Nov 11, 2019

• #6

Thank you for all the help with this matter. I was able to retrieve the data I needed by using these criteria. Notify: IIf(DateDiff("d",[Expiration_Date_Next_Review_Date],Date())>59,"Need Review","Up To Date").

I appreciate the help!

 

theDBguy

I’m here to help

Staff member

Local time

Today, 06:20

Joined

Oct 29, 2018

Messages

21,449

• Nov 11, 2019

• #7

Onehalf03 said:

Thank you for all the help with this matter. I was able to retrieve the data I needed by using these criteria. Notify: IIf(DateDiff("d",[Expiration_Date_Next_Review_Date],Date())>59,"Need Review","Up To Date").

I appreciate the help!

Click to expand...

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.