DateDiff Function

[Joanne]

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.

 

[John Spencer MVP]

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

 

[John W. Vinson]

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?

 

[Joanne]

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.

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

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.

 

[Joanne]

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.

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.

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.