UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Calculating Correct Date, Access 2010    
 
   
LilAnnCC1
post Nov 15 2017, 02:44 PM
Post#1



Posts: 719
Joined: 31-May 04
From: Wisconsin, USA


I have a query that is calculating various dates based on a hire date.

CODE
SELECT tblEmployees.EmpID, tblEmployees.BirthDate, tblEmployees.HireDate, Year(Date())-Year([BirthDate]) AS Age, DateAdd("d",60,[HireDate]) AS Ins, DateSerial(Year([Ins]),Month([Ins])+1,1) AS EnrollIns, DateAdd("d",180,[HireDate]) AS 401k
FROM tblEmployees
WHERE (((tblEmployees.TermDate) Is Null) AND ((tblEmployees.EmpTypeID)=481));


This works great unless the Ins date (DateAdd("d",60,[HireDate])) falls on the first of the month, then the EnrollIns forces the date to the next month.

How would I correct this so that if the INS date falls on December 1st the EnrollIns date will calculate 12/1/2017 and not 1/1/2018 as it is doing now?

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
RJD
post Nov 15 2017, 03:07 PM
Post#2


UtterAccess VIP
Posts: 7,834
Joined: 25-October 10
From: Gulf South USA


Hi:

QUOTE
How would I correct this so that if the INS date falls on December 1st the EnrollIns date will calculate 12/1/2017 and not 1/1/2018 as it is doing now?

Are you saying that if the Ins date is NOT on the 1st of the month, then add a month to get the EnrollIns, but if it IS on the first of the month, then don't add any days at all? Or is 12/1/2017 a typo (and you actually meant 12/31/2017) and you mean if the Ins is NOT on the 1st, then add a month, but if it IS on the first of the month, calculate EnrollIn as the END of that same month?

Not real clear from the description (I know you understand what you want, but it may not be clear to us). Perhaps you could give us some data examples showing what you want to happen under different circumstances. Then I think we can help ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
LilAnnCC1
post Nov 15 2017, 03:23 PM
Post#3



Posts: 719
Joined: 31-May 04
From: Wisconsin, USA


Thank you!

In the list of employees, I calculate Insurance Eligible Date which is 60 days after the hire date. We can only start insurance on the 1st day of the following month that employee becomes eligible. So the enrollment date calculates to the 1st day of the next month.

In the case of one employee (as of right now) the Eligible Date is 12/1/2017, but the Enrollment date calculates to 1/1/2018. So yes, you are correct--if it falls on the first--the enrollment date needs to show 12/1/17 and not 1/1/18. However, if the Eligible Date falls on 12/2/2017, the 1/1/2018 would be correct. If Eligible date falls on 11/30/2017, then enrollment would be 12/1/2017.

Thank you for your help!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
RJD
post Nov 15 2017, 03:38 PM
Post#4


UtterAccess VIP
Posts: 7,834
Joined: 25-October 10
From: Gulf South USA


Okay, try this for the EnrollIns ...

DateSerial(Year([Ins]),Month([Ins])+ IIf(Day([Ins])=1,0,1),1) AS EnrollIns

Also note that your Age calculation is not correct, sometimes not producing the right result. There are lots of examples around here, but if you have difficulty finding them, let me know and I will dredge up an example.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
LilAnnCC1
post Nov 15 2017, 03:45 PM
Post#5



Posts: 719
Joined: 31-May 04
From: Wisconsin, USA


Oh Joe, I'm so jumping for Joy! You should really see my happy dance!

The age is just to get me close to the year. 401k is only available to those over 21. I should probably change it as I'm always harping on everyone when they take shortcuts!

The one that I use is on main forms and reports is actually
DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))

Is this correct?

Thank you again!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
RJD
post Nov 15 2017, 03:53 PM
Post#6


UtterAccess VIP
Posts: 7,834
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad you are doing your Happy Dance!

As to the age, yes, your calculation looks (and tests) correct (although I would use Date() instead of Now()). There are several approaches to this, but the key is checking to see if the birthdate has yet occurred this year. I generally use this very slightly different calculation ...

DateDiff("yyyy",[BirthDate],Date())+(Format([BirthDate],"mmdd")>Format(Date(),"mmdd"))

... but, as I said, there are several approaches that work equally well.

Good luck with your project ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:44 AM