Date dilema

[JOM]

I have to calculate an outstanding rqst.

A request can come in today and be finished today or at a later date. This
date can be in the current month or later month. What I would like to do is
calculate at the begining of the next month what is outstanding from the
previous month i.e., still pending or completed but completed in later month
other than the date month that the request was received.

E.g., today is 01/02/06 getting outstanding rqst for the previous month
Rqst Date status Status Completion Date
12/30/05 Pending ---
12/30/05 Completed 1/01/06
12/30/05 Completed 12/30/05

Therefore total outstanding as of 12/30/05 = 2

 

[walkbet]

Clearly, if "Pending" is the status, then the item is outstanding.
That's easy.

As an "or" test, for the dates, I'd use a combination of testing the
RqstDateStatus month and Year against the CompletionDate Month and
Year. If they are not the same, then the item would be considered
outstanding. You can combine the Month fuction and the Year function to
do this.

hth-

Betsy

 

[mmb]

Try using Dcount

dcount("[Rqst Date]","TableName","[Completion Date] > TESTDATE")

of course, setting your testdate for end of month or user entered date,

Hope this helps,