DateDiif vs DateAdd

[BlueStarBrite]

Ok, so I need help figuring out a DateDiff/DateAdd solution. I have been searching the forum but havent come across exactly what I need.

I have 3 main variables that I want to use in my calculation: 1)Today's date 2)ApprovalDate 3)ExpirationDate

I know how to calculate 90 days prior to the Expiration date:
DateAdd ("d",-90,[ExpirationDate])

My problem is: I want to query for all people in the database whose expiration date is 90 days prior to their ApprovalDate based on today's date. Make sense In other words, when I run the query for 'today', pull a list where expiration date is 90 days out.

After researching, I am thinking I need to use the DateDiff function. However, I tried several versions, and can't quite get it to return the results I want. (Ex I have tried: DateDiff("d",Date(), ExpirationDate - ApprovalDate = 90)

I will be using this query for a report as well as a form that I want to trigger as a 'reminder list'

Please let me know where I am going wrong. TIA

 

[HiTechCoach]

note:

DateDiff("d",Date(), ExpirationDate - ApprovalDate = 90) is not valid since the third parameter must be a date (ExpirationDate - ApprovalDate = 90 does not a evaluate to a date)

Try somethng like:

Where (DateAdd ("d",-90,[ExpirationDate]) = Date() ) 
   AND ( DateDiff("d", ExpirationDate, ApprovalDate)  = 90)

You may also want to check out:
Reminders

Hope this helps ...

 

[stopher]

DATEADD and DATEDIFF really come into their own when dealing with units other than days e.g. hours, months years etc. The reason for this is that dates are store in the unit of days (the whole number), and part days (the fraction after the decimal point see here). So you can just use basic arithmetic to compare days.

But I'm struggling to understand what you are asking for....

My problem is: I want to query for all people in the database whose expiration date is 90 days prior to their ApprovalDate based on today's date.

I don't understand this.

In other words, when I run the query for 'today', pull a list where expiration date is 90 days out.

I understand this. You can get this with:
ExpirationDate=date()+90. Or if you want all records within 90 days then ExpirationDate<=date()+90. But I don't understand where ApprovalDate comes into it.

hth
Chris

 

[Galaxiom]

Dates are actually stored as a long number of days since 1900. Times are the decimal fractions of a day. So when working entirely with days, instead of using DateDiff and DateAdd you can usually simply add and subract the dates. If you are using whole days you don't need to worry about slight errors of days due to different times being involved.

Your DateDiff expression is trying to use a condition in the second date argument. This is the problem there.

I don't really understand the question but it will be easier to do by just adding and subracting dates than using the Date functions. You want something vaguely like:
WHERE SomeDate-Date()<90 AND AnotherDate-SomeDate=SomethingElse

 

[BlueStarBrite]

note:

DateDiff("d",Date(), ExpirationDate - ApprovalDate = 90) is not valid since the third parameter must be a date (ExpirationDate - ApprovalDate = 90 does not a evaluate to a date)

Try somethng like:

Where (DateAdd ("d",-90,[ExpirationDate]) = Date() ) 
   AND ( DateDiff("d", ExpirationDate, ApprovalDate)  = 90)

You may also want to check out:
Reminders

Hope this helps ...

Thank you - I will give this code a try today. I didn't even think to use both functions together. And the Reminders link you sent is exactly what I am looking for once I get pass my code problems. Thanks!

 

[BlueStarBrite]

DATEADD and DATEDIFF really come into their own when dealing with units other than days e.g. hours, months years etc. The reason for this is that dates are store in the unit of days (the whole number), and part days (the fraction after the decimal point see here). So you can just use basic arithmetic to compare days.

But I'm struggling to understand what you are asking for....
I don't understand this.

I understand this. You can get this with:
ExpirationDate=date()+90. Or if you want all records within 90 days then ExpirationDate<=date()+90. But I don't understand where ApprovalDate comes into it.

hth
Chris

Let's see if I can clarify my question, I think I got confused myself when I added in the ApprovalDate:

PersonA:
Approval Date = 7/7/2008
Expiration Date = 7/7/2009
Today's Date = 4/8/2009

Today's date (which is 90 days prior to the expiration date), PersonA should appear when I run the query.

I am using the approval date to calculate the expiration date (the expiration date will always be 1 year from the Approval Date). So I should have initially said: pull a list where expiration date is exactly 90 days prior to today (That's why I was thinking of using: DateAdd ("d",-90,[ExpirationDate]) . I think I was overthinking. Sorry for the confusion! (newbie mistake) But thanks for all your suggestions.