Date comparison query

[khurram7x]

I need to generate a query where I've a requirement to show all employees who have their training validity expired once we compare the Completion Date field in one table with ValidityYears (validation on certificate in years) of training in another table.

Attaching relationship screenshot, when I try to use DateDiff function in expression builder as:

IIf((DateDiff("yyyy",[tblEmployeeTrainings]![CompletionDate],Date())>=[tblWorkTrainings]![ValidityYears]),1,0)

It gives correct result when it compare to years, but it can't compare months and days. for example completion date of certificate was at April 30, 2014. If it compare it with current date it should show me false part of IIf because it still has over a month left in expiry from now, but it shows me true because query is comparing only years. I've spent several hours but could not figure out.

How should i rewrite the query in expression builder so that i compare to the months and days level PLEASE?

Thanks.

 

[plog]

to show all employees who have their training validity expired once we compare the Completion Date field in one table with ValidityYears (validation on certificate in years) of training in another table

Huh? Your verbs aren't jiving with your nouns. You can't compare a date (CompletionDate) with a number (ValidityYears). You can only compare similar data types.

I'm guessing you want to compare CompletionDate + ValidityYears to the current date, but I'm not entirely sure. Could you re-explain, possibly with example data?

 

[spikepl]

About DateDiff: http://www.sqlteam.com/article/datediff-function-demystified

Use DateAdd instead.

 

[khurram7x]

I'm guessing you want to compare CompletionDate + ValidityYears to the current date, but I'm not entirely sure. Could you re-explain, possibly with example data?

Thanks plog, yes you understood correct... i want to compare CompleteDate + ValidityYear to the current date. For e.g. there's a safety course on Hydrogen Sulphide (H2S) with a1 year validity. Now if the course was passed by the person on, let's say, on 30th Apr, 14. System should show renewal of course exactly after 1 year, i.e. on 30th Apr, 15 and that is all i'm looking for.

 

[khurram7x]

About DateDiff: http://www.sqlteam.com/article/datediff-function-demystified

Use DateAdd instead.

Thanks, article helped. I didn't get a time how to use DateAdd function to get work but DatePart idea from the article helped me solve the issue.
Posting the query renewed query below.

 

[khurram7x]

Huh? Your verbs aren't jiving with your nouns. You can't compare a date (CompletionDate) with a number (ValidityYears). You can only compare similar data types.

I'm guessing you want to compare CompletionDate + ValidityYears to the current date, but I'm not entirely sure. Could you re-explain, possibly with example data?

I've rewritten the query like this:
IIf(((DateDiff("yyyy",[tblEmployeeTrainings]![CompletionDate],Date()) >= [tblWorkTrainings]![ValidityYears]) And (DatePart('y',Date()) >= DatePart('y',[tblEmployeeTrainings]![CompletionDate]))),1,0)

I'm getting correct results now. Query is quite complex. Is there a better way to do this in future please?
I appreciate any better idea/advice on this please.

 

[khurram7x]

About DateDiff: http://www.sqlteam.com/article/datediff-function-demystified

Use DateAdd instead.

Thank you, got your point on DateAdd(). It is much easier than what I got myself into with DatePart and DateDiff, and it indeed is the actual solution.