Hello,
I have the following in a query field and I'm getting a strange result:
Iif ([Type Of Day]="Personal" or [Type of Day]="Jury Duty" Or [Type of
Day]="Carryover", "0",DateDiff("h",[OLP Begin Date],[OLP End Date]/24))

The purpose of the query is to get a return for 1/2 vacation days, but it's
returning things like -908315 for one vacation day. I thought I was doing
this right, but obviously I'm not.

Thank you very much for any help you can provide.

Perhaps what you want is the following.

Iif ([Type Of Day] IN ("Personal","Jury Duty","Carryover"),
0,DateDiff("h",[OLP Begin Date],[OLP End Date])/24)


One problem is that your statement was dividing the end date by 24 before
calculating the number of hours between the Begin date and the end date. I'm
not sure you are going to get the desired result anyway, since I don't know
that the Begin date and End date contain a date and a time (other than the
default midnight).


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Joanne wrote:
> Hello,
> I have the following in a query field and I'm getting a strange result:
> Iif ([Type Of Day]="Personal" or [Type of Day]="Jury Duty" Or [Type of
> Day]="Carryover", "0",DateDiff("h",[OLP Begin Date],[OLP End Date]/24))
>
> The purpose of the query is to get a return for 1/2 vacation days, but it's
> returning things like -908315 for one vacation day. I thought I was doing
> this right, but obviously I'm not.
>
> Thank you very much for any help you can provide.

On Thu, 18 Jun 2009 11:56:02 -0700, Joanne <(E-Mail Removed)>
wrote:

>Hello,
>I have the following in a query field and I'm getting a strange result:
>Iif ([Type Of Day]="Personal" or [Type of Day]="Jury Duty" Or [Type of
>Day]="Carryover", "0",DateDiff("h",[OLP Begin Date],[OLP End Date]/24))
>
>The purpose of the query is to get a return for 1/2 vacation days, but it's
>returning things like -908315 for one vacation day. I thought I was doing
>this right, but obviously I'm not.
>
>Thank you very much for any help you can provide.

You're dividing the OLP End Date by 24 - that'll give you a date sometime in
1904, because of the way dates are stored!

I think it's just a misplaced parenthesis: try

Iif ([Type Of Day]="Personal" or [Type of Day]="Jury Duty" Or [Type of
Day]="Carryover", 0 ,DateDiff("h",[OLP Begin Date],[OLP End Date])/24)

Note that I'm using a numeric 0 rather than a text string "0" since DateDiff
returns a number, and they should match.

Hmmmm... thinking about it... this will give the duration in twenty-fourths of
an hour, which is probably not what you want. What SHOULD the result contain?
Days? Days in half-day increments? hours?

--

John W. Vinson [MVP]

Thank you very much. It now returns a value in days and, you were right, it
isn't giving me the 1/2 day, but at least it works! Thanks again.

"John Spencer MVP" wrote:

> Perhaps what you want is the following.
>
> Iif ([Type Of Day] IN ("Personal","Jury Duty","Carryover"),
> 0,DateDiff("h",[OLP Begin Date],[OLP End Date])/24)
>
>
> One problem is that your statement was dividing the end date by 24 before
> calculating the number of hours between the Begin date and the end date. I'm
> not sure you are going to get the desired result anyway, since I don't know
> that the Begin date and End date contain a date and a time (other than the
> default midnight).
>
>
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Joanne wrote:
> > Hello,
> > I have the following in a query field and I'm getting a strange result:
> > Iif ([Type Of Day]="Personal" or [Type of Day]="Jury Duty" Or [Type of
> > Day]="Carryover", "0",DateDiff("h",[OLP Begin Date],[OLP End Date]/24))
> >
> > The purpose of the query is to get a return for 1/2 vacation days, but it's
> > returning things like -908315 for one vacation day. I thought I was doing
> > this right, but obviously I'm not.
> >
> > Thank you very much for any help you can provide.
>

Hello,
Many thanks for the help. The result I'm trying to get is in days, which is
working, but there are times when an employee wants to take only 1/2 day, so
I thought I would enter the datediff in hours but that didn't work either. I
need to have it displayed as 1.5 days or 2.5 days, or whole days, if that's
possible. Part of the problem is that the employeee might take all of Tuesday
and then only half of Wed. Ugh.

"JimBurke via AccessMonster.com" wrote:

> Are you simply trying to translate the number of 'off days' into a number of
> half-days? e.g. if they had 2 days off, you want to return 4 half-days? if
> that's the case, I would think you would just calculate the number of days
> off using datediff and then multiply that by 2. Maybe I'm misinterpreting the
> purpose of the query.
>
> Joanne wrote:
> >Hello,
> >I have the following in a query field and I'm getting a strange result:
> >Iif ([Type Of Day]="Personal" or [Type of Day]="Jury Duty" Or [Type of
> >Day]="Carryover", "0",DateDiff("h",[OLP Begin Date],[OLP End Date]/24))
> >
> >The purpose of the query is to get a return for 1/2 vacation days, but it's
> >returning things like -908315 for one vacation day. I thought I was doing
> >this right, but obviously I'm not.
> >
> >Thank you very much for any help you can provide.
>
> --
> Jim Burke
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200906/1
>
>