Query returns two records for each row - one blank

[Cloonalt]

I don't know if this is enough information for anybody to help, but I hope so.

I have a query that looks at a date that an employee took as a vacation day. I look at that date and return "vacation year" depending on what begindate and enddate it fell between.

I'm trying to do that with this selection criteria in a query:

VacationYear: IIf([Date_off] Between [vacationstartyear] And [vacationendyear],[vacationyear])

It produces the correct data and an additional blank record for each row. And I don't know why.

I'd really appreciate any help.

SQL below.

SELECT tblTransaction.TransactionID, tblTransaction.EmployeeID, PTOType.PTOTypeID, tblEmployee.fname, tblEmployee.lname, tblTransaction.Date_Off, PTOType.PTOType, tblTransaction.Transaction_Date, Format([Date_Off],"yyyy") AS [Year], tblTransaction.Hours_Used, tblTransaction.archived, tblEmployee.StartDate, IIf([Date_off] Between [vacationstartyear] And [vacationendyear],[vacationyears]) AS VacationYear
FROM (PTOType INNER JOIN (tblEmployee INNER JOIN tblTransaction ON tblEmployee.EmployeeID = tblTransaction.EmployeeID) ON PTOType.PTOTypeID = tblTransaction.Transaction_Type) INNER JOIN qryVacationYears ON tblEmployee.EmployeeID = qryVacationYears.EmployeeID
GROUP BY tblTransaction.TransactionID, tblTransaction.EmployeeID, PTOType.PTOTypeID, tblEmployee.fname, tblEmployee.lname, tblTransaction.Date_Off, PTOType.PTOType, tblTransaction.Transaction_Date, Format([Date_Off],"yyyy"), tblTransaction.Hours_Used, tblTransaction.archived, tblEmployee.StartDate, IIf([Date_off] Between [vacationstartyear] And [vacationendyear],[vacationyears])
ORDER BY tblTransaction.TransactionID, tblTransaction.EmployeeID, PTOType.PTOTypeID;

 

[PHV]

Why using the GROUP BY clause if you don't need any aggregate function ? You may consider a SELECT DISTINCT query.
The syntax of the IIf function is:
IIf(condition, True part, False part)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cloonalt]

So the selection criteria I'm using is

VacationYear: IIf([Date_off] Between [vacationstartyear] And [vacationendyear] And [transaction_type]=2,[vacationyears])

and you mean that I should be using:

VacationYear: IIf([Date_off] Between [vacationstartyear] And [vacationendyear] And [transaction_type]=2,[vacationyears], "")

Doesn't fix it.

Thanks.

 

[lespaul]

Aren't you checking if the YEAR of Date_Off is between vacationstartyear and vacationendyear?

SHouldn't it be:

IIf(Format([Date_Off],"yyyy") Between [vacationstartyear] And [vacationendyear] And [transaction_type]=2,[vacationyears], "")

Leslie