Need help with calculating future dates

[mlcampeau]

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:

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
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
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)

[FishVal]

Hi, colleague.

So basically you need to generate date Jan-1-<Some year>.
DataSerial with Year function will be useful here.
Something like.

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

Switch(expr1, value1, expr2, value2, ...., True, valueDefault)
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.

Regards,
Fish

[mlcampeau]

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.

[mlcampeau]

FYI, using the switch statement:

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.