asked on

Min of Dates

Experts, I have a report that groups Dates as in below. But now I want to change this based on the min of ([tblTransactions].[LatestShipDate]), tblLetterOfCredit].[FinalMaturity], tblLetterOfCredit].[InitialExpireDate] and to keep the formatting as "mmmm yyyy".

How to do this? thank you

Here is what I am using now but want to make the change as stated:
Dates: IIf(IsNull([tblTransactions].[LatestShipDate]),Format([tblLetterOfCredit].[FinalMaturity],"mmmm yyyy"),Format([tblTransactions.LatestShipDate],"mmmm yyyy"))

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdvsa

ASKER

whoa... I will check this out. thank you...

pdvsa

ASKER

Patrick, pretty crafty. I think the module is way above me and not sure how to incorporate in the report.

I used the SWITCH. However, it seems to select a MIN of " " if one of the dates are Null. I would need for it to exclude a Null and just select the MIN out of any values that are NOT NULL.

I hope that makes sense. thank you. If the Module takes care of this then I might want to use the Module.. let me know.

thank you.

Patrick Matthews

I would have thought that Switch could handle that. Even if not, then the VBA approach should have no trouble with the nulls.

Can you post a smallish sample file?

pdvsa

ASKER

I somehow missed the post. Will try to post tom am.. Thanks

pdvsa

ASKER

Patrick: attached is a pared down version. The autoexec opens and click "year" then the report button. I have the module in as well. I am thinking the module is the way to go. There is only 1 report in the db. Thank you for the help. attached is 2k7.
New---use-this-for-EE.zip

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdvsa

ASKER

Its like reading Chinese to me. You are too good. Thanks a mill... Will test when get to computer.

pdvsa

ASKER

I think i get it now.