DateDiff with null fields-calculate using specific dates

  • Thread starter Windsorcat via AccessMonster.com
  • Start date

W

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!
 
Ad

Advertisements

J

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
 
G

Guest

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

Advertisements

W

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.

Jeff said:
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!
 

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

Top