Diaplay only if date is 6 months or less

[reb0101]

I am trying to create a query here and I am completely lost on how to work this.
I have two functions I’m trying to complete.
My company has many employees that have to travel extensively.
They are trying to keep track of leave dates, return dates, and total days they are traveling.
This one I figured out.
It takes leave date (and displays it when you run the query) and return date and gives you total days.
This is the SQL for the ‘working’ query:

SELECT LEAVE.TYPE, LEAVE.LEAVE_DATE, LEAVE.RETURN_DATE, DateDiff("d",[LEAVE_DATE],[RETURN_DATE]) AS Expr1
FROM LEAVE;

So, all’s well with this one.
Now the one I am stumped on.

The same people have a lot of international travel as well and not all are bright enough to keep up with how long they have until their passport expires.
They have a form they will input the expiration date on their passport.
That is kept in a table and I want to create a query that will take that value (“PASSPORT_EXP”)
And display it only if that expiration is 6 months less.
The total query would consist of first name, last name and passport expiration.
If there are say 100 people and 5 of them have passports with expiration dates within 6 months or less, it would only display the first and last name and exp date of those 5 when the query is run.
Any ideas on the SQL to do this?

 

[Brianwarnock]

You will need a Where clause such as
Where Datediff("m",Date(),[passport_exp]) < 6

Brian

 

[DCrake]

Simple Software Solutions

Add this column to your query

Exp:IIf(DateDiff("d",Date(),ExpireDate) < 133,"Expiry date warning,"")

This checks to se if the difference between today's date and the known expiry date is under 133 days (half a year) then display a warning message, otherwise leave blank.

CodeMaster:

 

[Brianwarnock]

Codemaster why use days when you can use months especially as the criteria is 6 months.

Brian