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

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" <liznewhome@yahoo.com> wrote in message 
news:1185230907.965770.314380@e9g2000prf.googlegroups.com...
> 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
> 

On Jul 23, 4:00 pm, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> 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" <liznewh...@yahoo.com> wrote in message
>
> news:1185230907.965770.314380@e9g2000prf.googlegroups.com...
>
>
>
> > 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?

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

"AccessNeophyte" <liznewhome@yahoo.com> wrote in message 
news:1185232229.117863.188890@z24g2000prh.googlegroups.com...
> On Jul 23, 4:00 pm, "Jeff Boyce" <nonse...@nonsense.com> wrote:
>> 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" <liznewh...@yahoo.com> wrote in message
>>
>> news:1185230907.965770.314380@e9g2000prf.googlegroups.com...
>>
>>
>>
>> > 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?
> 

On Jul 23, 4:38 pm, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> 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
>
> "AccessNeophyte" <liznewh...@yahoo.com> wrote in message
>
> news:1185232229.117863.188890@z24g2000prh.googlegroups.com...
>
>
>
> > On Jul 23, 4:00 pm, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> >> 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" <liznewh...@yahoo.com> wrote in message
>
> >>news:1185230907.965770.314380@e9g2000prf.googlegroups.com...
>
> >> > 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

AccessNeophyte wrote:

>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())

-- 
Marsh
MVP [MS Access]

On Jul 24, 12:00 am, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> > 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.
>
> 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.

--

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