problem with DateDiff expression

[AccessNeophyte]

Can someone tell me what's wrong with my expression? Maybe I'm using
"Now()" incorrectly.

I want to get a client's "L O S" (Length of Stay), which is usually
the difference between the Discharge Date and the Admit Date, unless
the client has not discharged, (null Discharge Date). In that case,
it would be the difference between today (Now()) and the Admit Date.

=DateDiff("d",IIf(Nz([DC_DischargeDate],[Now()],[AdmitDate]),
[DC_DischargeDate],[AdmitDate]))

When I try this, I get "Wrong number of arguments"

help?
Liz

 

[Jeff Boyce]

Liz

The problem probably isn't with Now(), but you still don't want to use that.
Now() returns date AND time ... instead, use Date() to get just the date
portion.

Take a look at Access HELP on the DateDiff() and IIF() functions, as well as
the Nz() function. You know your data better, but I'll guess that you could
make do with something like (untested):

DateDiff("d",Nz([DC_DischargeDate],Date())-[AdmitDate])

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[AccessNeophyte]

Liz

The problem probably isn't with Now(), but you still don't want to use that.
Now() returns date AND time ... instead, use Date() to get just the date
portion.

Take a look at Access HELP on the DateDiff() and IIF() functions, as well as
the Nz() function. You know your data better, but I'll guess that you could
make do with something like (untested):

DateDiff("d",Nz([DC_DischargeDate],Date())-[AdmitDate])

Regards

Jeff Boyce
Microsoft Office/Access MVP

Can someone tell me what's wrong with my expression? Maybe I'm using
"Now()" incorrectly.

I want to get a client's "L O S" (Length of Stay), which is usually
the difference between the Discharge Date and the Admit Date, unless
the client has not discharged, (null Discharge Date). In that case,
it would be the difference between today (Now()) and the Admit Date.

=DateDiff("d",IIf(Nz([DC_DischargeDate],[Now()],[AdmitDate]),
[DC_DischargeDate],[AdmitDate]))

When I try this, I get "Wrong number of arguments"

help?
Liz- Hide quoted text -

- Show quoted text -

Does it make a difference if I'm using the expression as a
ControlSource for a contol on a form?

 

[Jeff Boyce]

vs. what else?

Do you have an "=" in front of the expression when you use it as a Control
Source? Are the other fields "available" in the same form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Liz

The problem probably isn't with Now(), but you still don't want to use
that.
Now() returns date AND time ... instead, use Date() to get just the date
portion.

Take a look at Access HELP on the DateDiff() and IIF() functions, as well
as
the Nz() function. You know your data better, but I'll guess that you
could
make do with something like (untested):

DateDiff("d",Nz([DC_DischargeDate],Date())-[AdmitDate])

Regards

Jeff Boyce
Microsoft Office/Access MVP

Can someone tell me what's wrong with my expression? Maybe I'm using
"Now()" incorrectly.

I want to get a client's "L O S" (Length of Stay), which is usually
the difference between the Discharge Date and the Admit Date, unless
the client has not discharged, (null Discharge Date). In that case,
it would be the difference between today (Now()) and the Admit Date.

=DateDiff("d",IIf(Nz([DC_DischargeDate],[Now()],[AdmitDate]),
[DC_DischargeDate],[AdmitDate]))

When I try this, I get "Wrong number of arguments"

help?
Liz- Hide quoted text -

- Show quoted text -

Does it make a difference if I'm using the expression as a
ControlSource for a contol on a form?

 

[AccessNeophyte]

vs. what else?

Do you have an "=" in front of the expression when you use it as a Control
Source? Are the other fields "available" in the same form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Liz
The problem probably isn't with Now(), but you still don't want to use
that.
Now() returns date AND time ... instead, use Date() to get just the date
portion.
Take a look at Access HELP on the DateDiff() and IIF() functions, as well
as
the Nz() function. You know your data better, but I'll guess that you
could
make do with something like (untested):
DateDiff("d",Nz([DC_DischargeDate],Date())-[AdmitDate])
Regards
Jeff Boyce
Microsoft Office/Access MVP

Can someone tell me what's wrong with my expression? Maybe I'm using
"Now()" incorrectly.
I want to get a client's "L O S" (Length of Stay), which is usually
the difference between the Discharge Date and the Admit Date, unless
the client has not discharged, (null Discharge Date). In that case,
it would be the difference between today (Now()) and the Admit Date.
=DateDiff("d",IIf(Nz([DC_DischargeDate],[Now()],[AdmitDate]),
[DC_DischargeDate],[AdmitDate]))
When I try this, I get "Wrong number of arguments"
help?
Liz- Hide quoted text -
- Show quoted text -

Does it make a difference if I'm using the expression as a
ControlSource for a contol on a form?- Hide quoted text -

- Show quoted text -

Yes and Yes.

Thank you so much for the help! I plugged in your expression and
played with it until it returned no errors.

This is what finally worked:

=-(DateDiff("d",Nz([DC_DischargeDate],Date()),[AdmitDate]))

I had to use - after the = because otherwise it showed a negative
number. The correct number, mind you, just negative. Is that because
I've got the Discharge Date first?


Thanks,
Liz

 

[Marshall Barton]

Can someone tell me what's wrong with my expression? Maybe I'm using
"Now()" incorrectly.

I want to get a client's "L O S" (Length of Stay), which is usually
the difference between the Discharge Date and the Admit Date, unless
the client has not discharged, (null Discharge Date). In that case,
it would be the difference between today (Now()) and the Admit Date.

=DateDiff("d",IIf(Nz([DC_DischargeDate],[Now()],[AdmitDate]),
[DC_DischargeDate],[AdmitDate]))

When I try this, I get "Wrong number of arguments"

I don't understand what the combination of Nz and IIf is
supposed to do, normally, you should one or the other.
Because Nz might return an inappropriate data type. try
using:

=DateDiff("d", AdmitDate, IIf(DC_DischargeDate Is Null,
Date(), DC_DischargeDate))

If you used Nz, it would be:

=DateDiff("d", AdmitDate, Nz(DC_DischargeDate, Date())

 

[Jamie Collins]

The problem probably isn't with Now(), but you still don't want to use that.
Now() returns date AND time ... instead, use Date() to get just the date
portion.

Access/Jet has but one temporal data type named DATETIME, which
*always* returns a time element (as an aid memoir, get into the habit
of always writing DATETIME values in full -- ISO 8601 if it's an
international forum, please). If the OP is using a NULL end date value
to indicate the period of the current state, it makes perfect sense to
use the current timestamp NOW() in place of NULL when querying the
table, time being a continuum and all.

I think the OP's issue is with time granules and time representation
of periods (closed-open, closed-closed, etc). Take the period:

[ #2007-07-23 09:00:00#, #2007-07-24 17:00:00#)

Say that this is taken to mean a two day stay in the OP's model.

Using DATEDIFF to calculate days always rounds down, so if I'm using
closed-closed representation (where both start and end dates falls
within the period; suits BETWEEN constructs) to model a two day stay
like this:

[ #2007-07-23 00:00:00#, #2007-07-24 23:59:59#]

then DATEDIFF will calculate this as

SELECT DATEDIFF('D', #2007-07-23 00:00:00#, #2007-07-24 23:59:59#)

returns 1 (day). In practice, I find I have to add one second (one of
the disadvantages of closed-closed representation).

If the smallest time granule in the OP's table is one day then perhaps
close-open representation (where the end date does not fall within the
period; suits Access/Jets floating point nature of DATETIME) is better
e.g. modelled as (note the closing parenthesis denoting an open end
date):

[ #2007-07-23 00:00:00#, #2007-07-25 00:00:00#)

SELECT DATEDIFF('D', #2007-07-23 00:00:00#, #2007-07-25 00:00:00#)

returns 2 (days).

I've never been comfortable with using closed-open representation,
because using #2007-07-25 00:00:00# to model #2007-07-24 17:00:00# is
non-intuitive for me.

Perhaps a better approach is to model the actual start dates and
'round' them as appropriate when calculating. Take this extreme
example:

[ #2007-07-23 23:59:59#, #2007-07-24 00:00:01#]

Frankly, I'd question whether a two second stay should be considered a
two day stay (!!) but here's how I'd do it:

SELECT
DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, #2007-07-23
23:59:59#), #1990-01-01 00:00:00#) AS start_date_rounded_down,
DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, #2007-07-24
00:00:01#), #1990-01-02 00:00:00#) AS end_date_rounded_up,
DATEDIFF('D', DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#,
#2007-07-23 23:59:59#), #1990-01-01 00:00:00#),
DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, #2007-07-24
00:00:01#), #1990-01-02 00:00:00#)) AS interval_days_spanned

Jamie.

--

 

[John Spencer]

YES. DateDiff calculates the difference in time intervals from first date
to second date. So if the first date is after the second date the number of
intervals is negative.

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