Find the End of the Month For a Report Field

[Jeff Garrison]

Hello all -

I'm using Access 07 to report on a SQL backend database. What I'm trying to
do is to find out how many calls remain open at the end of the month. The
fields I have are:

Ent_Date Date call was opened
Cls_Date Date call was closed

What my logic is is to put an unbound control on the report that calculates
the End of the Month for the Ent_Date...but I'm not sure how to go about it.
After I find the End of the Month for the Ent_Date, I'll look to see if the
Cls_Date falls before or after the EOM Date and do a logic test...

I think I can get the rest, but need help on the EOM function.

Thanks in advance.

JeffG

 

[John Spencer]

IIF(Format(Ent_Date,"yyyy-mm")=Format(Cls_Date,"yyyy-mm")
,"Closed","Not Closed")

EndOfMonth expression if you choose to go another way:
DateSerial(Year(Ent_Date),Month(Ent_Date)+1,0)

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County