# Thread: Compare Dates (A 97)

1. ## Compare Dates (A 97)

I have three date fields on a form: ReceiptDate, InitialResponseDate, FinalResponseDate, CurrentRevisionDate

On my report I'd like to be able to compare the latter three date fields and choose the most current (or greatest), then subtract the first date field from the result.
(ex. ReceiptDate = 12/01/02 , InitialResponseDate = 01/01/03 , FinalResponseDate = 02/01/03 , CurrentRevisionDate = 03/01/03 ....so the return should be 03/01/03 - 12/01/02, or roughly 91 days).

What's the syntax for comparing these three dates in the Field: row in a QBE window?

2. ## Re: Compare Dates (A 97)

Something like:

TurnaroundTime: IIF([CurrentRevisionDate]>[FinalResponseDate],[CurrentRevisionDate]-[ReceiptDate],[FinalResponseDate]-[ReceiptDate])

?

If the dates are not real date fields, you might have to put cdate() around them to perform the calculation.

3. ## Re: Compare Dates (A 97)

Cecilia,

That works great, but how do I incorporate the InitialResponseDate bit in here...can I nest another IIF or IF stmt within the IIF stmt you provided?

I guess I should have clarified that two of the three date fields are independent of each other (ie. FinalResponseDate doesn't necessarily have a corresponding InitialResponseDate that preceeds it, but a CurrentRevisionDate WOULD have to have either an IntitialResponseDate or a FinalResponseDate preceeding it. The process is such that a "Final" response can be issued without the need for an "Initial" response first.)

And the fields are true date fields, so luckily I won't have to convert....

Thanks,

4. ## Re: Compare Dates (A 97)

You can nest a bunch of if statements if you need to. Another way to do it is you can do a domain aggregate function in the query. It's slow, but I do it when there's no other way to get what I want. This assumes that your dates are in a table. It would look like this:

MaxDate: DMax("MyDateField","tblDateTable")

I'm not sure how you'd do it in your program, but you just have to get creative.

5. ## Re: Compare Dates (A 97)

Am I correct that not all three of the latter dates need to occur in a single record, but if present, FinalResponseDate will not be earlier than InitialResponseDate, and CurrentRevisionDate will not be earlier than InitialResponseDate or FinalResponseDate? In that case, you can use the following expression:

IIf(IsNull([CurrentRevisionDate]),IIf(IsNull([FinalResponseDate]),[InitialResponseDate],[FinalResponseDate]),[CurrentRevisionDate])-[ReceiptDate]

6. ## Re: Compare Dates (A 97)

Hans,

You are correct in that assumption...the expression you provided works great.
Thanks to both you and Cecilia for the assistance!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•