DateDiff partly failing

R

Robbin

I have an expression that seems to work fine, unless the [EndDateofRank] is
populated. Iif the [EndDateofRank] is populated, it subtracts it from the
[AcademicYearEnding]. I can see that this is exactly what the expression
says, but not what I need. I need the difference between the BegDateofRank
and the [EndDateofRank] if the [EndDateofRank] is populated.

YearsInRank:
IIf(Month(Date())<7,TYears.AcademicYearEnding-DatePart("yyyy",nz([EndDateofRank],[BegDateofRank]))-1,TYears.AcademicYearEnding-DatePart("yyyy",nz([EndDateofRank],[BegDateofRank])))

Seems like the [AcademicYearEnding] and the [EndDateofRank] should be
switched, but I can't make it work.

Can anyone help?

Thank you!
 
J

John Spencer

PERHAPS:

YearsInRank:
IIF(EndDateOfRank is Null, AcademicYearEnding - Year(BegDateOfRank),
Year(EndDateOfRank)-Year(BegDateOfRank)) - IIF(Month(Date())<7,1,0)

OR
IIF(EndDateOfRank is Null, AcademicYearEnding, Year(EndDateOfRank) ) -
Year(BegDateOfRank) - IIF(Month(Date())<7,1,0)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
R

Robbin

John,
You must be getting close, but the (If 7/1) part is to accomodate the fiscal
year. I'm asking it not to add another year until 7/1. Does that make sense?
--
Robbin


John Spencer said:
PERHAPS:

YearsInRank:
IIF(EndDateOfRank is Null, AcademicYearEnding - Year(BegDateOfRank),
Year(EndDateOfRank)-Year(BegDateOfRank)) - IIF(Month(Date())<7,1,0)

OR
IIF(EndDateOfRank is Null, AcademicYearEnding, Year(EndDateOfRank) ) -
Year(BegDateOfRank) - IIF(Month(Date())<7,1,0)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Robbin said:
I have an expression that seems to work fine, unless the [EndDateofRank] is
populated. Iif the [EndDateofRank] is populated, it subtracts it from the
[AcademicYearEnding]. I can see that this is exactly what the expression
says, but not what I need. I need the difference between the
BegDateofRank
and the [EndDateofRank] if the [EndDateofRank] is populated.

YearsInRank:
IIf(Month(Date())<7,TYears.AcademicYearEnding-DatePart("yyyy",nz([EndDateofRank],[BegDateofRank]))-1,TYears.AcademicYearEnding-DatePart("yyyy",nz([EndDateofRank],[BegDateofRank])))

Seems like the [AcademicYearEnding] and the [EndDateofRank] should be
switched, but I can't make it work.

Can anyone help?

Thank you!
Click to expand...
Click to expand...
 
J

John Spencer

Since I am not sure of the logic you want, all I can suggest is that you
adjust the IIF (Month(Date())<1 part of the expression. Right now it will
subtract 1 if the current date is before July 1. Do you want it to do
something else? If so, what?

Otherwise, describe in words what you want the calculation to be. Something
like:
I want to calculate year in rank based on the difference in years between
Begin date and end date.
If there is no end date I want to use the Academic year in place of end
date.
If I use Academic year I want to subtract 1 year if the current date is
before July 1.
If I use begin date and end date I do not want to subtract 1
IF begin date and end date both have values I want the calculation to be in
whole years only. So the day and month of EndDate must be after the day and
month of begin date.


You might be better off using
Nz(EndDateOfRank,DateSerial(AcademicYearEnding,6,30)) to calculate the date
to be used

And this expression to calculate the number of whole years.
DateDiff("yyyy",BegDateOfRank,Nz(EndDateOfRank,DateSerial(AcademicYearEnding,6,30)))
+
Format(BegDateOfRank,"mmdd") >
Format(Nz(EndDateOfRank,DateSerial(AcademicYearEnding,6,30)),"mmdd")

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

Robbin said:
John,
You must be getting close, but the (If 7/1) part is to accomodate the
fiscal
year. I'm asking it not to add another year until 7/1. Does that make
sense?
--
Robbin


John Spencer said:
PERHAPS:

YearsInRank:
IIF(EndDateOfRank is Null, AcademicYearEnding - Year(BegDateOfRank),
Year(EndDateOfRank)-Year(BegDateOfRank)) - IIF(Month(Date())<7,1,0)

OR
IIF(EndDateOfRank is Null, AcademicYearEnding, Year(EndDateOfRank) ) -
Year(BegDateOfRank) - IIF(Month(Date())<7,1,0)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Robbin said:
I have an expression that seems to work fine, unless the [EndDateofRank]
is
populated. Iif the [EndDateofRank] is populated, it subtracts it from
the
[AcademicYearEnding]. I can see that this is exactly what the
expression
says, but not what I need. I need the difference between the
BegDateofRank
and the [EndDateofRank] if the [EndDateofRank] is populated.

YearsInRank:
IIf(Month(Date())<7,TYears.AcademicYearEnding-DatePart("yyyy",nz([EndDateofRank],[BegDateofRank]))-1,TYears.AcademicYearEnding-DatePart("yyyy",nz([EndDateofRank],[BegDateofRank])))

Seems like the [AcademicYearEnding] and the [EndDateofRank] should be
switched, but I can't make it work.

Can anyone help?

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
R

Robbin

Sorry about the ambiguity. I would like the expression to give me the years
in rank subtracting the end date from the start date, or use the clock where
there is no end date. BUT- I don't want it to add the next year on the
change of the calendar year, rather on the change of the fiscal year, which
is 7/1. So, if it's between January and July, it has to subract 1. After
July 1, it can simply calcualate the actual difference.

And the funny thing is, all of that part is working correctly. It just
won't subract the enddate from the start date. I ignores the enddate and
continues to use the clock date when an endate is entered.
I hope this helps.
--
Robbin


John Spencer said:
Since I am not sure of the logic you want, all I can suggest is that you
adjust the IIF (Month(Date())<1 part of the expression. Right now it will
subtract 1 if the current date is before July 1. Do you want it to do
something else? If so, what?

Otherwise, describe in words what you want the calculation to be. Something
like:
I want to calculate year in rank based on the difference in years between
Begin date and end date.
If there is no end date I want to use the Academic year in place of end
date.
If I use Academic year I want to subtract 1 year if the current date is
before July 1.
If I use begin date and end date I do not want to subtract 1
IF begin date and end date both have values I want the calculation to be in
whole years only. So the day and month of EndDate must be after the day and
month of begin date.


You might be better off using
Nz(EndDateOfRank,DateSerial(AcademicYearEnding,6,30)) to calculate the date
to be used

And this expression to calculate the number of whole years.
DateDiff("yyyy",BegDateOfRank,Nz(EndDateOfRank,DateSerial(AcademicYearEnding,6,30)))
+
Format(BegDateOfRank,"mmdd") >
Format(Nz(EndDateOfRank,DateSerial(AcademicYearEnding,6,30)),"mmdd")

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

Robbin said:
John,
You must be getting close, but the (If 7/1) part is to accomodate the
fiscal
year. I'm asking it not to add another year until 7/1. Does that make
sense?
--
Robbin


John Spencer said:
PERHAPS:

YearsInRank:
IIF(EndDateOfRank is Null, AcademicYearEnding - Year(BegDateOfRank),
Year(EndDateOfRank)-Year(BegDateOfRank)) - IIF(Month(Date())<7,1,0)

OR
IIF(EndDateOfRank is Null, AcademicYearEnding, Year(EndDateOfRank) ) -
Year(BegDateOfRank) - IIF(Month(Date())<7,1,0)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have an expression that seems to work fine, unless the [EndDateofRank]
is
populated. Iif the [EndDateofRank] is populated, it subtracts it from
the
[AcademicYearEnding]. I can see that this is exactly what the
expression
says, but not what I need. I need the difference between the
BegDateofRank
and the [EndDateofRank] if the [EndDateofRank] is populated.

YearsInRank:
IIf(Month(Date())<7,TYears.AcademicYearEnding-DatePart("yyyy",nz([EndDateofRank],[BegDateofRank]))-1,TYears.AcademicYearEnding-DatePart("yyyy",nz([EndDateofRank],[BegDateofRank])))

Seems like the [AcademicYearEnding] and the [EndDateofRank] should be
switched, but I can't make it work.

Can anyone help?

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
R

Robbin

Hi Folks,

I'm still struggling with this formula -- it's so close, but something is
still wrong: YearsInRank:
IIf(Month(Date())>7,DateDiff("yyyy",[BegDateofRank],nz([EndDateofRank],[FiscalYear]),DateDiff("yyyy",[BegDateofRank],nz([EndDateofRank],[FiscalYear])-1)

I'd like this formula to calculate the years in rank based on the Beginning
date of Rank, minus the end date of rank, using the [FiscalYear] field as the
end date of rank where that field is blank. I created a field called
[FiscalYear], which returns a value of 07/01"YYYY" for whichever year is in
the AcademicYearEnding field.

The formula seems to be working fine, except that where the begining date is
07/01/2006 and the end date is 07/01/2007, I get a 0 in the Years in Rank
field UNLESS I roll the computer clock past July 1 in 2008. Only then does
it give me a 1 in the Year in rank field. Since both begining and ending
dates preceed the FiscalYear of 07/01/2008, it should simply return a value
of 1, right?

I've tried about 10 different suggestions and iterations, to no avail.
Thank you for any help you can offer.

Thank you.
--
Robbin


Robbin said:
Sorry about the ambiguity. I would like the expression to give me the years
in rank subtracting the end date from the start date, or use the clock where
there is no end date. BUT- I don't want it to add the next year on the
change of the calendar year, rather on the change of the fiscal year, which
is 7/1. So, if it's between January and July, it has to subract 1. After
July 1, it can simply calcualate the actual difference.

And the funny thing is, all of that part is working correctly. It just
won't subract the enddate from the start date. I ignores the enddate and
continues to use the clock date when an endate is entered.
I hope this helps.
--
Robbin


John Spencer said:
Since I am not sure of the logic you want, all I can suggest is that you
adjust the IIF (Month(Date())<1 part of the expression. Right now it will
subtract 1 if the current date is before July 1. Do you want it to do
something else? If so, what?

Otherwise, describe in words what you want the calculation to be. Something
like:
I want to calculate year in rank based on the difference in years between
Begin date and end date.
If there is no end date I want to use the Academic year in place of end
date.
If I use Academic year I want to subtract 1 year if the current date is
before July 1.
If I use begin date and end date I do not want to subtract 1
IF begin date and end date both have values I want the calculation to be in
whole years only. So the day and month of EndDate must be after the day and
month of begin date.


You might be better off using
Nz(EndDateOfRank,DateSerial(AcademicYearEnding,6,30)) to calculate the date
to be used

And this expression to calculate the number of whole years.
DateDiff("yyyy",BegDateOfRank,Nz(EndDateOfRank,DateSerial(AcademicYearEnding,6,30)))
+
Format(BegDateOfRank,"mmdd") >
Format(Nz(EndDateOfRank,DateSerial(AcademicYearEnding,6,30)),"mmdd")

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

Robbin said:
John,
You must be getting close, but the (If 7/1) part is to accomodate the
fiscal
year. I'm asking it not to add another year until 7/1. Does that make
sense?
--
Robbin


:

PERHAPS:

YearsInRank:
IIF(EndDateOfRank is Null, AcademicYearEnding - Year(BegDateOfRank),
Year(EndDateOfRank)-Year(BegDateOfRank)) - IIF(Month(Date())<7,1,0)

OR
IIF(EndDateOfRank is Null, AcademicYearEnding, Year(EndDateOfRank) ) -
Year(BegDateOfRank) - IIF(Month(Date())<7,1,0)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have an expression that seems to work fine, unless the [EndDateofRank]
is
populated. Iif the [EndDateofRank] is populated, it subtracts it from
the
[AcademicYearEnding]. I can see that this is exactly what the
expression
says, but not what I need. I need the difference between the
BegDateofRank
and the [EndDateofRank] if the [EndDateofRank] is populated.

YearsInRank:
IIf(Month(Date())<7,TYears.AcademicYearEnding-DatePart("yyyy",nz([EndDateofRank],[BegDateofRank]))-1,TYears.AcademicYearEnding-DatePart("yyyy",nz([EndDateofRank],[BegDateofRank])))

Seems like the [AcademicYearEnding] and the [EndDateofRank] should be
switched, but I can't make it work.

Can anyone help?

Thank you!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filtering by Month 1
Invalid use of null 3
Running total in query 2
URGENT HELP NEEDED WITH GETTING AVERAGES BY QUARTER 1
Iif Query 5
Sort on Date 1
Using BETWEEN AND with DatePart 1
dates 1

Top