By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,606 Members | 1,525 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,606 IT Pros & Developers. It's quick & easy.

Need help with calculating future dates

Expert 100+
P: 296
Hi guys,
I am trying to run a query that calculates when employees become eligible for supplemental vacation. This occcurs once the employee has reached 5, 10, 15, etc years of service. I got the query running great, it shows the correct dates, (5, 10, etc years after the HireDate) but I just found out that they don't want to know the exact anniversary date. People become entitled to their vacation January 1st of the year of their anniversary date. So, for example, say their 5th anniversary is May 11, 2009, their eligibility date should then be Jan. 1, 2009. The query I have right now shows May 11, 2009. How can I get it to show Jan. 1 of the anniversary year? Here's my query so far:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Main].[LastName] & ", " & [Main].[FirstName] AS Name, Employee.ID, EmplOrganization.DepartmentCode, Department.Desc, EmplOrganization.DivisionCode, Division.Desc, Job.Title, EmplStatus.StatusCode, Employee.HireDate, EmplDays.TotalDaysEmployed, EmplDays.Years, EmplDays.Months, EmplDays.Days, IIf(DateAdd("m",60,[HireDate])>Now(),DateAdd("m",60,[HireDate]), IIf(DateAdd("m",120,[HireDate])>Now(),DateAdd("m",120,[HireDate]), IIf(DateAdd("m",180,[HireDate])>Now(),DateAdd("m",180,[HireDate]), IIf(DateAdd("m",240,[HireDate])>Now(),DateAdd("m",240,[HireDate]), IIf(DateAdd("m",300,[HireDate])>Now(),DateAdd("m",300,[HireDate]), IIf(DateAdd("m",360,[HireDate])>Now(),DateAdd("m",360,[HireDate]), IIf(DateAdd("m",420,[HireDate])>Now(),DateAdd("m",420,[HireDate]), IIf(DateAdd("m",480,[HireDate])>Now(),DateAdd("m",480,[HireDate]),"")))))))) AS SupplementalEligibility, Category.Code, CategoryReason.Desc
  2. FROM Main RIGHT JOIN (Job RIGHT JOIN (Department RIGHT JOIN (((((Employee LEFT JOIN EmplDays ON Employee.ID = EmplDays.EmployeeID) LEFT JOIN (CategoryReason RIGHT JOIN (Category RIGHT JOIN EmplCategory ON Category.Code = EmplCategory.CategoryCode) ON CategoryReason.Code = EmplCategory.CategoryReasonCode) ON Employee.ID = EmplCategory.EmployeeID) LEFT JOIN EmplJob ON Employee.ID = EmplJob.EmployeeID) LEFT JOIN (Division RIGHT JOIN EmplOrganization ON Division.Code = EmplOrganization.DivisionCode) ON Employee.ID = EmplOrganization.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.EmployeeID) ON Department.Code = EmplOrganization.DepartmentCode) ON Job.Code = EmplJob.JobCode) ON Main.ID = Employee.MainID
  3. WHERE (((EmplStatus.StatusCode)="A" Or (EmplStatus.StatusCode)="LOA") AND ((CategoryReason.Desc)="Permanent") AND ((EmplCategory.Active)=True) AND ((EmplJob.Active)=True) AND ((EmplOrganization.Active)=True) AND ((EmplStatus.Active)=True));
Also, in my iif statement in the query, I get an #Error if none of the conditions evaluate to true. My "" doesn't seem to work because I'm dealing with dates. How can I get an empty string or something like NA to show up instead of #Error? (i.e. After 40 years of services there is no more supplemental vacation)
Sep 14 '07 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 2,653
Hi, colleague.

So basically you need to generate date Jan-1-<Some year>.
DataSerial with Year function will be useful here.
Something like.
Expand|Select|Wrap|Line Numbers
  1. DateSerial (Year(dteDateReturned),1,1)
BTW I suggest you to use Switch function which works exactly like iif's nested through <false> section. Despite Switch function doesn't have <Default/False> section you can easily simulate it with one of the following
Expand|Select|Wrap|Line Numbers
  1. Switch(expr1, value1, expr2, value2, ...., True, valueDefault)
  2. Nz(Switch(expr1, value1, expr2, value2, ....), valueDefault)
Towards your 2nd question.
Queries return variant type to a field. There is no problem to get strings and dates simultaneously in one column. The reason for #Error is definitely not a types conflict.

Sep 14 '07 #2

Expert 100+
P: 296
DataSerial with Year function will be useful here....
Thanks Fish, the DateSerial worked perfectly.

BTW I suggest you to use Switch function which works exactly like iif's nested through <false> section.....
What would be the advantage of using the Switch function over the nested iif's? I've never used the switch function before.

Towards your 2nd question.
Queries return variant type to a field. There is no problem to get strings and dates simultaneously in one column. The reason for #Error is definitely not a types conflict.
Hmm...Not sure why I'm getting this #Error. As I said, if I try "" it comes up, I tried "NA" and that doesn't work. I tried Now() and that DID work, so that's why I originally thought it didn't like the empty string. I'll keep looking at that part.
Sep 14 '07 #3

Expert 100+
P: 296
FYI, using the switch statement:
Expand|Select|Wrap|Line Numbers
  1. Switch(DateAdd("m",60,[HireDate])>Now(),DateAdd("m",60,[HireDate]), DateAdd("m",120,[HireDate])>Now(),DateAdd("m",120,[HireDate]), DateAdd("m",180,[HireDate])>Now(),DateAdd("m",180,[HireDate]), DateAdd("m",240,[HireDate])>Now(),DateAdd("m",240,[HireDate]), DateAdd("m",300,[HireDate])>Now(),DateAdd("m",300,[HireDate]), DateAdd("m",360,[HireDate])>Now(),DateAdd("m",360,[HireDate]), DateAdd("m",420,[HireDate])>Now(),DateAdd("m",420,[HireDate]), DateAdd("m",480,[HireDate])>Now(),DateAdd("m",480,[HireDate]),True,"") AS SupplementaryEligibility
works great and leaves a blank rather than #Error for those with more than 40 years service to the company.
Sep 14 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.