DateDiff And Null field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Johnny - 28 Feb 2005 11:47 GMT

I have two fields in my table: Date Received And Date closed. I would like a
Date Diff on my form to show how many days I have had something if the Date
Closed field is Null (ie not closed). I have a field to show how many days
between received and closed but how many days open would be quite useful.
Also could i run a report to show all entries in the table that are not
closed and the number of days since receipt? Thanks from a newbie

Reply to this Message

Arvin Meyer - 28 Feb 2005 12:13 GMT

DateDiff("d", [Date Received], IIf(IsNull([DateClosed],Date(),[DateClosed]))

Signature

Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

> I have two fields in my table: Date Received And Date closed. I would like a
> Date Diff on my form to show how many days I have had something if the Date
> Closed field is Null (ie not closed). I have a field to show how many days
> between received and closed but how many days open would be quite useful.
> Also could i run a report to show all entries in the table that are not
> closed and the number of days since receipt? Thanks from a newbie

Reply to this Message

Johnny - 01 Mar 2005 20:03 GMT

Arvin, Thanks for the reply. I am getting a error message stating 'expression
has function containing the wrong no. of arguments. I can see that there is a
bracket missing and have tried to apply one in various places but am having
no joy. Any further advice?

> DateDiff("d", [Date Received], IIf(IsNull([DateClosed],Date(),[DateClosed]))
> > I have two fields in my table: Date Received And Date closed. I would like
[quoted text clipped - 5 lines]
> > Also could i run a report to show all entries in the table that are not
> > closed and the number of days since receipt? Thanks from a newbie

Reply to this Message

Arvin Meyer - 03 Mar 2005 12:28 GMT

Sorry, that's what happens when you type directly into a newsreader. Try:

DateDiff("d", [Date Received],
IIf(IsNull([DateClosed]),Date(),[DateClosed]))

I've add a closing parenthesis after the first DateClosed. (Again in the
newsreader <g>)

Signature

Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

> Arvin, Thanks for the reply. I am getting a error message stating 'expression
> has function containing the wrong no. of arguments. I can see that there is a
[quoted text clipped - 10 lines]
> > > Also could i run a report to show all entries in the table that are not
> > > closed and the number of days since receipt? Thanks from a newbie

Reply to this Message

Windsorcat - 25 Jan 2007 21:49 GMT

My problem is very similar to this except I need to enter a specific date to
get the # of days stayed in a month.

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

>Sorry, that's what happens when you type directly into a newsreader. Try:
>
[quoted text clipped - 8 lines]
>> > > Also could i run a report to show all entries in the table that are not
>> > > closed and the number of days since receipt? Thanks from a newbie

Reply to this Message