DateDiff And IIf

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that records a date received and a date closed field amongst
others in a table. I wish to count the number of days from date received
until current day if the date closed field has not been completed. I have
received the following advice, but it returns an arguments error.

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

I have tried to fix this but unfortunately I am too dumb!!
 
Johnny,

I think the use of IIf() in this case, is unnecessary. The Nz() function
would be a more appropriate choice:
DateDiff("d", [Date Received], Nz([Date Closed], Date())

But to answer your question about why the DateDiff statement fails; it
depends on what error you're getting. You said it returns an "arguments
error" - what's an "arguments error"?

It also depends on where you're using the statement - in a query, as a
control's RecordSource or DefaultValue, or in code?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Graham


Graham R Seach said:
Johnny,

I think the use of IIf() in this case, is unnecessary. The Nz() function
would be a more appropriate choice:
DateDiff("d", [Date Received], Nz([Date Closed], Date())

But to answer your question about why the DateDiff statement fails; it
depends on what error you're getting. You said it returns an "arguments
error" - what's an "arguments error"?

It also depends on where you're using the statement - in a query, as a
control's RecordSource or DefaultValue, or in code?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Johnny said:
I have a form that records a date received and a date closed field amongst
others in a table. I wish to count the number of days from date received
until current day if the date closed field has not been completed. I have
received the following advice, but it returns an arguments error.

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

I have tried to fix this but unfortunately I am too dumb!!
Click to expand...
Click to expand...
 
Graham.The error message is as follows...'The expression you entered has a
function containing the wrong number of arguments' The formula is entered
into the control source field of a text box.

Graham R Seach said:
Johnny,

I think the use of IIf() in this case, is unnecessary. The Nz() function
would be a more appropriate choice:
DateDiff("d", [Date Received], Nz([Date Closed], Date())

But to answer your question about why the DateDiff statement fails; it
depends on what error you're getting. You said it returns an "arguments
error" - what's an "arguments error"?

It also depends on where you're using the statement - in a query, as a
control's RecordSource or DefaultValue, or in code?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Johnny said:
I have a form that records a date received and a date closed field amongst
others in a table. I wish to count the number of days from date received
until current day if the date closed field has not been completed. I have
received the following advice, but it returns an arguments error.

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

I have tried to fix this but unfortunately I am too dumb!!
Click to expand...
Click to expand...
 
Johnny,

OK, then the problem is that either [Date Received] or [Date Closed] is
Null. In that case, you have to account for Null values:

So if you want to use your existing statement:
=DateDiff("d", Nz([Date Received], Nz([Date Closed], Date())),
IIf(IsNull([Date Closed]), Date(), [Date Closed]))

....but if you want to use mine:
=DateDiff("d", Nz([Date Received], Nz([Date Closed], Date())), Nz([Date
Closed], Date()))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Johnny said:
Graham.The error message is as follows...'The expression you entered has a
function containing the wrong number of arguments' The formula is entered
into the control source field of a text box.

Graham R Seach said:
Johnny,

I think the use of IIf() in this case, is unnecessary. The Nz() function
would be a more appropriate choice:
DateDiff("d", [Date Received], Nz([Date Closed], Date())

But to answer your question about why the DateDiff statement fails; it
depends on what error you're getting. You said it returns an "arguments
error" - what's an "arguments error"?

It also depends on where you're using the statement - in a query, as a
control's RecordSource or DefaultValue, or in code?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Johnny said:
I have a form that records a date received and a date closed field
amongst
others in a table. I wish to count the number of days from date
received
until current day if the date closed field has not been completed. I
have
received the following advice, but it returns an arguments error.

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

I have tried to fix this but unfortunately I am too dumb!!
Click to expand...
Click to expand...
Click to expand...
 
Graham


Graham R Seach said:
Johnny,

OK, then the problem is that either [Date Received] or [Date Closed] is
Null. In that case, you have to account for Null values:

So if you want to use your existing statement:
=DateDiff("d", Nz([Date Received], Nz([Date Closed], Date())),
IIf(IsNull([Date Closed]), Date(), [Date Closed]))

....but if you want to use mine:
=DateDiff("d", Nz([Date Received], Nz([Date Closed], Date())), Nz([Date
Closed], Date()))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Johnny said:
Graham.The error message is as follows...'The expression you entered has a
function containing the wrong number of arguments' The formula is entered
into the control source field of a text box.

Graham R Seach said:
Johnny,

I think the use of IIf() in this case, is unnecessary. The Nz() function
would be a more appropriate choice:
DateDiff("d", [Date Received], Nz([Date Closed], Date())

But to answer your question about why the DateDiff statement fails; it
depends on what error you're getting. You said it returns an "arguments
error" - what's an "arguments error"?

It also depends on where you're using the statement - in a query, as a
control's RecordSource or DefaultValue, or in code?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that records a date received and a date closed field
amongst
others in a table. I wish to count the number of days from date
received
until current day if the date closed field has not been completed. I
have
received the following advice, but it returns an arguments error.

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

I have tried to fix this but unfortunately I am too dumb!!
Click to expand...
Click to expand...
Click to expand...
 
Graham


Graham R Seach said:
Johnny,

OK, then the problem is that either [Date Received] or [Date Closed] is
Null. In that case, you have to account for Null values:

So if you want to use your existing statement:
=DateDiff("d", Nz([Date Received], Nz([Date Closed], Date())),
IIf(IsNull([Date Closed]), Date(), [Date Closed]))

....but if you want to use mine:
=DateDiff("d", Nz([Date Received], Nz([Date Closed], Date())), Nz([Date
Closed], Date()))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Johnny said:
Graham.The error message is as follows...'The expression you entered has a
function containing the wrong number of arguments' The formula is entered
into the control source field of a text box.

Graham R Seach said:
Johnny,

I think the use of IIf() in this case, is unnecessary. The Nz() function
would be a more appropriate choice:
DateDiff("d", [Date Received], Nz([Date Closed], Date())

But to answer your question about why the DateDiff statement fails; it
depends on what error you're getting. You said it returns an "arguments
error" - what's an "arguments error"?

It also depends on where you're using the statement - in a query, as a
control's RecordSource or DefaultValue, or in code?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that records a date received and a date closed field
amongst
others in a table. I wish to count the number of days from date
received
until current day if the date closed field has not been completed. I
have
received the following advice, but it returns an arguments error.

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

I have tried to fix this but unfortunately I am too dumb!!
Click to expand...
Click to expand...
Click to expand...
 
Graham
If my date closed field has a date in it I do not want to return a value. Ie
the job is closed so I have no need to know how many days have expired. I am
still getting a count of the total number of days.

Graham R Seach said:
Johnny,

OK, then the problem is that either [Date Received] or [Date Closed] is
Null. In that case, you have to account for Null values:

So if you want to use your existing statement:
=DateDiff("d", Nz([Date Received], Nz([Date Closed], Date())),
IIf(IsNull([Date Closed]), Date(), [Date Closed]))

....but if you want to use mine:
=DateDiff("d", Nz([Date Received], Nz([Date Closed], Date())), Nz([Date
Closed], Date()))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Johnny said:
Graham.The error message is as follows...'The expression you entered has a
function containing the wrong number of arguments' The formula is entered
into the control source field of a text box.

Graham R Seach said:
Johnny,

I think the use of IIf() in this case, is unnecessary. The Nz() function
would be a more appropriate choice:
DateDiff("d", [Date Received], Nz([Date Closed], Date())

But to answer your question about why the DateDiff statement fails; it
depends on what error you're getting. You said it returns an "arguments
error" - what's an "arguments error"?

It also depends on where you're using the statement - in a query, as a
control's RecordSource or DefaultValue, or in code?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that records a date received and a date closed field
amongst
others in a table. I wish to count the number of days from date
received
until current day if the date closed field has not been completed. I
have
received the following advice, but it returns an arguments error.

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

I have tried to fix this but unfortunately I am too dumb!!
Click to expand...
Click to expand...
Click to expand...
 
Johnny,

=IIf(IsNull([Date Closed])=False, Null, DateDiff("d", Nz([Date Received],
Date()), Date())

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Johnny said:
Graham
If my date closed field has a date in it I do not want to return a value.
Ie
the job is closed so I have no need to know how many days have expired. I
am
still getting a count of the total number of days.

Graham R Seach said:
Johnny,

OK, then the problem is that either [Date Received] or [Date Closed] is
Null. In that case, you have to account for Null values:

So if you want to use your existing statement:
=DateDiff("d", Nz([Date Received], Nz([Date Closed], Date())),
IIf(IsNull([Date Closed]), Date(), [Date Closed]))

....but if you want to use mine:
=DateDiff("d", Nz([Date Received], Nz([Date Closed], Date())),
Nz([Date
Closed], Date()))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Johnny said:
Graham.The error message is as follows...'The expression you entered
has a
function containing the wrong number of arguments' The formula is
entered
into the control source field of a text box.

:

Johnny,

I think the use of IIf() in this case, is unnecessary. The Nz()
function
would be a more appropriate choice:
DateDiff("d", [Date Received], Nz([Date Closed], Date())

But to answer your question about why the DateDiff statement fails; it
depends on what error you're getting. You said it returns an
"arguments
error" - what's an "arguments error"?

It also depends on where you're using the statement - in a query, as a
control's RecordSource or DefaultValue, or in code?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that records a date received and a date closed field
amongst
others in a table. I wish to count the number of days from date
received
until current day if the date closed field has not been completed. I
have
received the following advice, but it returns an arguments error.

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

I have tried to fix this but unfortunately I am too dumb!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top