How do I get the DMax(Date)

[Guest]

Here is my iif statement:
=IIf([TaxDocStatus]<>"Pending",DMax("[TxDocDateRcvd]","[TbleDocs]","[BorrowerID] =" & [BorrowerID]),"")

What am trying to get is th Max Date of a borrowers record if does not
contain a pending status on any one of its documents.

I have the following information in my table

John White
W2 2001 Pending
W2 2002 Complete 12/12/05
1090 2001 Complete 12/20/05

Smith May
W2 2002 Complete 12/18/05
W2 2003 Complete 12/13/05

***************************************************

So what I would like is to show only 12/18/05 for the 2nd record and "In
Progress" for the first record since one of the docs is still pending...

 

[John Vinson]

Here is my iif statement:
=IIf([TaxDocStatus]<>"Pending",DMax("[TxDocDateRcvd]","[TbleDocs]","[BorrowerID] =" & [BorrowerID]),"")

What am trying to get is th Max Date of a borrowers record if does not
contain a pending status on any one of its documents.

I have the following information in my table

John White
W2 2001 Pending
W2 2002 Complete 12/12/05
1090 2001 Complete 12/20/05

Smith May
W2 2002 Complete 12/18/05
W2 2003 Complete 12/13/05

***************************************************

So what I would like is to show only 12/18/05 for the 2nd record and "In
Progress" for the first record since one of the docs is still pending...

Do you have the date embedded in a Text or Memo field? If so... it's
much, much harder for Access to get at it. The text string "W2 2003
Complete 12/13/05" is not a date, and will not be recognized as a
date. If the date portion of the information is relevant, you should
really have a separate field for it.

As it is, you'll need to write some (possibly quite tricky) VBA code
to parse the string, find a substring that can be interpreted as a
date (might some records look like "W2 2002 Complete on the Fourth of
December" or some other date format)?

John W. Vinson[MVP]