DateDiff with null fields-calculate using specific dates

[Windsorcat via AccessMonster.com]

I am trying to get the # of days stayed in a particular month even if the
client is still open currently.

My expression is Expr: DateDiff("d",[Admission_Date],IIf(IsNull(
[Actual_Close_Date]),Date(),[Actual_Close_Date]))

My criteria for Admission_Date is >12/1/2006 and <12/31/06
criteria for Actual_Close_Date is <1/1/07 or is null

Example I have a client that was admitted 12/27/06 and is still open but the
datediff results are 29 days (4 days in December + 25 days in January) I
want my query to just show the 4 days in December.

The results I want are the days within the month of December the client has
stayed but only for December. I realize that using "Date" in my expression
uses the current date, but I don't know what to enter to get only December
info.?

Thanks in advance for any help!

 

[Jeff Boyce]

Do you mean you'll only ever want to know about "December", or do you mean
you want to know about "last month" (based on "today's" date)?

Take a look at the DateSerial() function in Access HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[sailcm]

I accomplished the same thing by creating a table with all the dates in the
year. Then creating a query that lists all the dates between the admission
and discharge date, this query includes a datepart field for month. Then I
used another query to count the days per month.

Hope this helps.

 

[Windsorcat via AccessMonster.com]

Good question-I want to know about different past months, so I guess I would
need to be able to plug in October, November, December.

I'll try the DateSerial. Thanks.

Do you mean you'll only ever want to know about "December", or do you mean
you want to know about "last month" (based on "today's" date)?

Take a look at the DateSerial() function in Access HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to get the # of days stayed in a particular month even if the
client is still open currently.

[quoted text clipped - 18 lines]

Thanks in advance for any help!