"IF" expression in a query

[Duncan Edment]

I have a query that returns the Annual Leave days recorded for a specific
employee. This query is as follows:

SELECT tblEmp.EmpFname, tblEmp.EmpLname, tblDaysOff.EmpID, tblDaysOff.VacDate,
tblDaysOff.VacID, 1 AS NumOfRecords, Year([vacDate]) AS VacDayYear,
tblDaysOff.fldNumHours, tblEmp.fldLeaveEntitlement
FROM tblEmp RIGHT JOIN tblDaysOff ON tblEmp.EmpID = tblDaysOff.EmpID
ORDER BY tblDaysOff.VacDate;

However, the query returns all dates recorded for the employee, when I only want
it to return dates in their current annual leave year. The basic logic of this
query is (I think):

if Month(Date()) < {staff leave month} then
RecordSet = leave day => #01/{leave month}/{displayed year} - 1# AND
<= #{last day of month}/{leave month} -
1/{displayed year}#
ELSE
RecordSet = leave day >= #01/{leave month}/{displayed year}# AND
<= #{last day of month}/{leave month} -
1/{displayed year} + 1#

How can I incorporate this into my query?

Many thanks for your help.

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

 

[Michel Walsh]

Hi,


DateSerial( [displayed year] + iif( Month( Now() ) < Month([leave
month] ), -1, 0) , Month( [leave month] ), 1 )




Hoping it may help,
Vanderghast, Access MVP