Count of days

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Linda RQ - 21 Apr 2008 02:24 GMT

Hi Everyone,

Using Access 2003 in 2000 format. I am finishing up my query and am stuck
on an expression to give me the total days my patients are on a ventilator.
For this calculation, the time the patient went on and off the ventilator
doesn't matter. If they started on it on 1/1 at 11:30 pm and came off at
2:00am on 1/3 the total days would be 3. 1/1 to 1/1 would be 1 and 1/1 to
1/2 would be 2. My StDtTm has a Date/Time format.

I have started to use the expression builder and I like it. I have no
programming education so it seems like a good tool for now and today, it
saved you all from helping me about 10 times. I may be using the wrong
function for this so let me know. I have the expression below but I don't
know what to put in the <<interval>> spot. I thought maybe "d" but that
didn't work and if I delete this place holder, I get an error.

1. What is it asking for?
2. Is this the right function to use for my expression?

DateDiff («interval», [ThpyStDtTm],[ThpyEndDtTm])

Thanks,
Linda

Reply to this Message

Duane Hookom - 21 Apr 2008 02:44 GMT

Use "D" or "d" as the interval. This will return the number of date changes
from the start date to the end date. It sounds like you want to add 1 to this
to calculate the days including the start and end date.
DateDiff ("D", [ThpyStDtTm],[ThpyEndDtTm] +1 )

Signature

Duane Hookom
Microsoft Access MVP

> Hi Everyone,
>
[quoted text clipped - 19 lines]
> Thanks,
> Linda

Reply to this Message

Linda RQ - 21 Apr 2008 02:44 GMT

Dudes! I got it! I typed my d without quotes the first time and when I
reread my question, I remembered I have to use quotes. The only problem is
that the expression seems to be ignoring the first day so if I add a +1 to
the end, it works right. Can someone explain this? My son thinks it's
because of the time factor but I am not sure that is it.

Thanks,
Linda

> Hi Everyone,
>
[quoted text clipped - 19 lines]
> Thanks,
> Linda

Reply to this Message

Rick Brandt - 21 Apr 2008 12:15 GMT

> Dudes! I got it! I typed my d without quotes the first time and
> when I reread my question, I remembered I have to use quotes. The
> only problem is that the expression seems to be ignoring the first
> day so if I add a +1 to the end, it works right. Can someone explain
> this? My son thinks it's because of the time factor but I am not
> sure that is it.

Hmm, most people complain about DateDiff in the other direction (the value
being one larger than they thought it should be).

DateDiff counts "boundaries crossed" so even DateDiff "d" using April 21,
2008 23:59:59 and April 22, 2008 00:00:00 should return a value of 1.

Not much of a complaint for days, but when using it for months comparing the
last second of one month with the first second of the next month returns 1
even though there is only one second of difference.

If the number you are seeing is too small by one that would seem to indicate
that you still have something incorrect.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message