Find the least recent date of four dates

[joshbell]

Hello,

I am trying to find the least recent date of four date fields in a
query. Is there a way to do this? I'm lost.

Thanks for your help!

jb

 

[Tom Ellison]

Dear Josh:

First, an important point. If you have 4 date columns whose values are
sufficiently related to one another that you can compare them, then there is
a real problem with how the table is constructed. I (or others) will give
you further information on this if you require it. Please just ask!

An immediate solution is to write a function that compares values and
returns the minimum. Are you up to writing VBA?

This solution assumes you are using Jet as your database engine. The
solution for MSDE is quite different.

Please let me know how I can best help you further.

Tom Ellison

 

[joshbell]

Hi Tom:

Thanks for the response.

Yes, I am using Jet as my database engine.

I am sure you are correct, our table is probably not set up very well
but I am wondering if we can do this without changing the table - a
lot has been built around it.

I don't have much practical VBA experience but I am familiar with it.
I could give it a shot.

I will give you the scenario and maybe that will help explain it.

We have a claim table [tblClaim] that we use the track different forms
of communication. The four dates in question are listed below:
1. A paid date [datClaimPaid]
2. A vendor can deny a claim [datArmoredClaimDenied}
3. The date a vendor acknowledges a claim [datVendorResponseDate]
4. The date we rescind the claim [datRescindArmored]

These dates are being compared against the date of a letter that was
sent to the vendor notifying them of the claim [datClaimLetterDate].

A vendor has 30 days to acknowledge a claim. A vendor has acknowledged
a claim if any of these four are not null. If they do not contact us by
one or more of these ways, we can penalize them $50 a day. If the first
point is contact is more than 30 days, we can penalize the vendor for
the days that are more than 30. For this exercise, we just want to know
the earliest of these four dates and assess the penalty from that. A
claim can have zero to four of these dates filled in - about half of
our claims have zero of these dates.

We would like to set up a query that dynamically asses the penalties on
a daily basis and then stops racking up the penalty once the claim has
been paid.

We really appreciate your help. We've been struggling with this for a
few days now.

Thanks again,

jb