How do I get the DMax(Date)

J

JOM

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...
 
J

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab Query question! 2
Please Help on Crosstab Question! 0
Crosstab Query Question 0
Crosstab division question! 0
iif statement 1
Calculations on my report! 0
Update a date on main Form 2
Date Question! 3

Top