Date() at fiscal year

[Robbin]

Hi,

I have an expression that works perfectly, (I got it here, I think), except
for one minor problem. I need the date to increase by one year, this year,
as of July 1 of this year. My professors don't get another year's experience
until they finish the second semester, on the fiscal year, July 1.

YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])

This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get the
wrong answer.

YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)+[CreditServiceYears])


Can anyone help? Thank you!

 

[Jeff Boyce]

Do you need to use an IF() or IIF() statement to evaluate how to handle a
date before 7/1 and after 7/1?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Robbin]

Dear Jeff,

I do believe you are right. Can you help me with what that would look like?
--
Robbin

Do you need to use an IF() or IIF() statement to evaluate how to handle a
date before 7/1 and after 7/1?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi,

I have an expression that works perfectly, (I got it here, I think), except
for one minor problem. I need the date to increase by one year, this year,
as of July 1 of this year. My professors don't get another year's experience
until they finish the second semester, on the fiscal year, July 1.

YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])

This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get the
wrong answer.

YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)
+[CreditServiceYears])


Can anyone help? Thank you!

 

[Robbin]

I have put this together out of my limited understanding:

YearsExperience:
IIf(DateSerial(Year(Date())<7,1,DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])),DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date()-1)+[CreditServiceYears]))

Seems to be working, and I tested it by changing my computer date to July 8,
and it still seems to work. But the question is, should it? Structuring it
the opposite way, (> vs. <) or putting the -1 in the opposite argument,
doesn't work. I fear my argument is sloppy.

Thank you,
--
Robbin

Dear Jeff,

I do believe you are right. Can you help me with what that would look like?
--
Robbin

Do you need to use an IF() or IIF() statement to evaluate how to handle a
date before 7/1 and after 7/1?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi,

I have an expression that works perfectly, (I got it here, I think), except
for one minor problem. I need the date to increase by one year, this year,
as of July 1 of this year. My professors don't get another year's experience
until they finish the second semester, on the fiscal year, July 1.

YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])

This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get the
wrong answer.

YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)
+[CreditServiceYears])


Can anyone help? Thank you!

 

[Jeff Boyce]

Robbin

My suggestion would be to throw all your potential "boundary conditions" at
it to see.

Right off the top, you'd need to know that it worked on dates immediately
before and immediately after your transition (?7/1/yyyy). You'd need to
know that it worked from one year to the next (2007, 2008, 2009). And you'd
need to know that it worked (i.e., did NOT trigger) for dates around the new
year (12/31 vs 1/1).

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have put this together out of my limited understanding:

YearsExperience:
IIf(DateSerial(Year(Date())<7,1,DateDiff("yyyy",[DateofHire],nz([DateofTTHir
e],Date())+[CreditServiceYears])),DateDiff("yyyy",[DateofHire],nz([DateofTTH
ire],Date()-1)+[CreditServiceYears]))

Seems to be working, and I tested it by changing my computer date to July 8,
and it still seems to work. But the question is, should it? Structuring it
the opposite way, (> vs. <) or putting the -1 in the opposite argument,
doesn't work. I fear my argument is sloppy.

Thank you,
--
Robbin

Dear Jeff,

I do believe you are right. Can you help me with what that would look like?
--
Robbin

Do you need to use an IF() or IIF() statement to evaluate how to handle a
date before 7/1 and after 7/1?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi,

I have an expression that works perfectly, (I got it here, I think),
except
for one minor problem. I need the date to increase by one year, this
year,
as of July 1 of this year. My professors don't get another year's
experience
until they finish the second semester, on the fiscal year, July 1.

YearsExperience:

DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])

This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get
the
wrong answer.

YearsExperience:

DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)
+[CreditServiceYears])


Can anyone help? Thank you!

 

[Robbin]

Hi Jeff,

Thank you for your pointers. I will put it through your tests and see if it
holds up. One thing I discovered was that putting the -1 after Date() caused
it to ignore the +[CreditServiceYears], so I had to move the -1 after
+[CreditServiceYears]. Seems to be working better now for that part.

If you get a chance, would you please take a hard look at it for logic?
Darned if it doesn't seem to be working backwards!
Thanks again.
--
Robbin

Robbin

My suggestion would be to throw all your potential "boundary conditions" at
it to see.

Right off the top, you'd need to know that it worked on dates immediately
before and immediately after your transition (?7/1/yyyy). You'd need to
know that it worked from one year to the next (2007, 2008, 2009). And you'd
need to know that it worked (i.e., did NOT trigger) for dates around the new
year (12/31 vs 1/1).

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have put this together out of my limited understanding:

YearsExperience:
IIf(DateSerial(Year(Date())<7,1,DateDiff("yyyy",[DateofHire],nz([DateofTTHir
e],Date())+[CreditServiceYears])),DateDiff("yyyy",[DateofHire],nz([DateofTTH
ire],Date()-1)+[CreditServiceYears]))

Seems to be working, and I tested it by changing my computer date to July 8,
and it still seems to work. But the question is, should it? Structuring it
the opposite way, (> vs. <) or putting the -1 in the opposite argument,
doesn't work. I fear my argument is sloppy.

Thank you,
--
Robbin

Dear Jeff,

I do believe you are right. Can you help me with what that would look like?
--
Robbin


:

Do you need to use an IF() or IIF() statement to evaluate how to handle a
date before 7/1 and after 7/1?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi,

I have an expression that works perfectly, (I got it here, I think),
except
for one minor problem. I need the date to increase by one year, this
year,
as of July 1 of this year. My professors don't get another year's
experience
until they finish the second semester, on the fiscal year, July 1.

YearsExperience:

DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])

This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get
the
wrong answer.

YearsExperience:

DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)
+[CreditServiceYears])


Can anyone help? Thank you!

 

[John Smith]

In case it may help, my route to mapping Financial onto Calendar years is just
to subtract the appropriate number of months to bring them back to Jan 1. For
your Financial year starting on July 1 that would be six. Assuming that your
CreditServiceYears is a number of years then it will need to go outside of the
DateDiff():

YearsExperience:
DateDiff("yyyy", DateofHire, DateAdd("m", -6, Nz(DateofTTHire, Date()))) +
CreditServiceYears

HTH
John
##################################
Don't Print - Save trees

 

[Robbin]

John,

That seems to stand up to the tests, and is certainly a cleaner solution.
Thank you!
--
Robbin

In case it may help, my route to mapping Financial onto Calendar years is just
to subtract the appropriate number of months to bring them back to Jan 1. For
your Financial year starting on July 1 that would be six. Assuming that your
CreditServiceYears is a number of years then it will need to go outside of the
DateDiff():

YearsExperience:
DateDiff("yyyy", DateofHire, DateAdd("m", -6, Nz(DateofTTHire, Date()))) +
CreditServiceYears

HTH
John
##################################
Don't Print - Save trees

I have an expression that works perfectly, (I got it here, I think), except
for one minor problem. I need the date to increase by one year, this year,
as of July 1 of this year. My professors don't get another year's experience
until they finish the second semester, on the fiscal year, July 1.
YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])
This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get the
wrong answer.
YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)+[CreditServiceYears])

 

[Robbin]

Hi John,

I just noticed that this is not affecting the nz part: [DateofTTHire]. I
tried this but it didn't work:

YearsExperience:

DateDiff("yyyy", [DateofHire], DateAdd("m", -6, Nz([DateofTTHire], DateAdd("m", -6, Date()))) +
[CreditServiceYears]

--
Robbin

In case it may help, my route to mapping Financial onto Calendar years is just
to subtract the appropriate number of months to bring them back to Jan 1. For
your Financial year starting on July 1 that would be six. Assuming that your
CreditServiceYears is a number of years then it will need to go outside of the
DateDiff():

YearsExperience:
DateDiff("yyyy", DateofHire, DateAdd("m", -6, Nz(DateofTTHire, Date()))) +
CreditServiceYears

HTH
John
##################################
Don't Print - Save trees

I have an expression that works perfectly, (I got it here, I think), except
for one minor problem. I need the date to increase by one year, this year,
as of July 1 of this year. My professors don't get another year's experience
until they finish the second semester, on the fiscal year, July 1.
YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])
This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get the
wrong answer.
YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)+[CreditServiceYears])