Calculating fiscal year for two dates in query

P

programmingrookie

I have a query based on a table which includes a start date and an end date,
which do not occur within the same fiscal year. I would like to pull all
records that are less than or equal to the fiscal year of the start date, and
greater than the fiscal year of the end date. I have no problems using FYear
to determine the fiscal year of one date, but when I try FYear on both I get
an error message that the query is too complicated. Is there another method
that can be used within a query?
 
M

Michel Walsh

Unless the query is really too complicated, that error message also occurs,
some times, when the ( ) are right in number, but produces illegal
statement, like:

( 2 + (3 *) 4 )


(you won't not necessary get THAT error for that exact syntax, it is just
for an illustration).


Can you cut and paste the SQL statement (from the SQL view of the query)
here?



Vanderghast, Access MVP
 
K

KARL DEWEY

I would like to pull all records that are less than or equal to the fiscal
year of the start date, and greater than the fiscal year of the end date.
You also did not say what your fiscal year is (everyone does not use the same
fiscal year).
I can not follow your criteria statement. Which records below would you
want to pull?
Record Start Date End Date
1 2006 2006
2 2006 2007
3 2006 2008
4 2006 2009
5 2006 2010
6 2007 2007
7 2007 2008
8 2007 2009
9 2007 2010
10 2008 2006
11 2008 2007
12 2008 2008
13 2008 2009
14 2008 2010
15 2009 2007
16 2009 2008
17 2009 2009
18 2009 2010
19 2010 2006
20 2010 2007
21 2010 2008
22 2010 2009
23 2010 2010
 
P

programmingrookie

Below is a copy of the complete SQL statement. What I envision is someone
will select a FY on a form, say 2008. Our FY is July to June. The query
will then pull everyone who started a position up to and including FY2008 but
is still in that position or ended after FY2008.

SELECT tblPersonnel.Department, tblPositions.PositionType,
tblPersonnel.LastName, tblPersonnel.FirstName, tblPersonnel.CurrentAssignNo,
tblPositions.OracleTitle, tblPositions.FTE, tblPositions.ApptLength,
tblPersonnelAction.ActionType, tblPersonnelAction.ActionDate,
tblPersonnelAction.ActionValue, tblSalComponents.FiscalYear,
tblSalComponents.BaseAfterRaise, tblSalComponents.EnhancementAR,
tblSalComponents.ProvostInitiativeAR, tblSalComponents.SpecInitMinHireAR,
tblSalComponents.AdminSuppSummer, tblSalComponents.AdminSuppAR,
tblSalComponents.AdminStipAR, tblSalComponents.Professorship,
tblSalComponents.TotalBeforeLongevity, tblPositions.PositionStartDate,
tblPositions.PositionEndDate,
Year([PositionEndDate])-IIf([PositionEndDate]<DateSerial(Year([PositionEndDate]),7,1),1,0)+1
AS FYearEnd,
Year([PositionStartDate])-IIf([PositionStartDate]<DateSerial(Year([PositionStartDate]),7,1),1,0)+1 AS FYearStart
FROM ((tblPersonnel INNER JOIN tblPositions ON tblPersonnel.EmployeeID =
tblPositions.EmployeeID) INNER JOIN tblSalComponents ON
tblPersonnel.EmployeeID = tblSalComponents.EmployeeID) INNER JOIN
tblPersonnelAction ON tblPersonnel.EmployeeID = tblPersonnelAction.EmployeeID
WHERE (((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FT",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear]) AND
((Year([PositionEndDate])-IIf([PositionEndDate]<DateSerial(Year([PositionEndDate]),7,1),1,0)+1)>=[Forms]![frmReports]![FiscalYear])
AND
((Year([PositionStartDate])-IIf([PositionStartDate]<DateSerial(Year([PositionStartDate]),7,1),1,0)+1)<=[Forms]![frmReports]![FiscalYear]))
OR (((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FT",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"PD",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"PD",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FN",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FN",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FX",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FX",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"AP",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"AP",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear]))
ORDER BY tblPersonnel.Department;
 
P

programmingrookie

Below is the complete SQL statement. What I envision is being able to select
a FY on a form, and pull positions that were active on or before that FY but
did not end until after the FY. Our FY is July - June.

SELECT tblPersonnel.Department, tblPositions.PositionType,
tblPersonnel.LastName, tblPersonnel.FirstName, tblPersonnel.CurrentAssignNo,
tblPositions.OracleTitle, tblPositions.FTE, tblPositions.ApptLength,
tblPersonnelAction.ActionType, tblPersonnelAction.ActionDate,
tblPersonnelAction.ActionValue, tblSalComponents.FiscalYear,
tblSalComponents.BaseAfterRaise, tblSalComponents.EnhancementAR,
tblSalComponents.ProvostInitiativeAR, tblSalComponents.SpecInitMinHireAR,
tblSalComponents.AdminSuppSummer, tblSalComponents.AdminSuppAR,
tblSalComponents.AdminStipAR, tblSalComponents.Professorship,
tblSalComponents.TotalBeforeLongevity, tblPositions.PositionStartDate,
tblPositions.PositionEndDate,
Year([PositionEndDate])-IIf([PositionEndDate]<DateSerial(Year([PositionEndDate]),7,1),1,0)+1
AS FYearEnd,
Year([PositionStartDate])-IIf([PositionStartDate]<DateSerial(Year([PositionStartDate]),7,1),1,0)+1 AS FYearStart
FROM ((tblPersonnel INNER JOIN tblPositions ON tblPersonnel.EmployeeID =
tblPositions.EmployeeID) INNER JOIN tblSalComponents ON
tblPersonnel.EmployeeID = tblSalComponents.EmployeeID) INNER JOIN
tblPersonnelAction ON tblPersonnel.EmployeeID = tblPersonnelAction.EmployeeID
WHERE (((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FT",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear]) AND
((Year([PositionEndDate])-IIf([PositionEndDate]<DateSerial(Year([PositionEndDate]),7,1),1,0)+1)>=[Forms]![frmReports]![FiscalYear])
AND
((Year([PositionStartDate])-IIf([PositionStartDate]<DateSerial(Year([PositionStartDate]),7,1),1,0)+1)<=[Forms]![frmReports]![FiscalYear]))
OR (((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FT",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"PD",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"PD",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FN",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FN",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FX",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FX",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"AP",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"AP",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear]))
ORDER BY tblPersonnel.Department;
 
K

KARL DEWEY

Try this --
SELECT tblPersonnel.Department, tblPositions.PositionType,
tblPersonnel.LastName, tblPersonnel.FirstName, tblPersonnel.CurrentAssignNo,
tblPositions.OracleTitle, tblPositions.FTE, tblPositions.ApptLength,
tblPersonnelAction.ActionType, tblPersonnelAction.ActionDate,
tblPersonnelAction.ActionValue, tblSalComponents.FiscalYear,
tblSalComponents.BaseAfterRaise, tblSalComponents.EnhancementAR,
tblSalComponents.ProvostInitiativeAR, tblSalComponents.SpecInitMinHireAR,
tblSalComponents.AdminSuppSummer, tblSalComponents.AdminSuppAR,
tblSalComponents.AdminStipAR, tblSalComponents.Professorship,
tblSalComponents.TotalBeforeLongevity, tblPositions.PositionStartDate,
tblPositions.PositionEndDate, Year(DateAdd("m",6,[PositionEndDate])) AS
FYearEnd, Year(DateAdd("m",6,[PositionStartDate])) AS FYearStart
FROM ((tblPersonnel LEFT JOIN tblPositions ON tblPersonnel.EmployeeID =
tblPositions.EmployeeID) LEFT JOIN tblSalComponents ON
tblPersonnel.EmployeeID = tblSalComponents.EmployeeID) LEFT JOIN
tblPersonnelAction ON tblPersonnel.EmployeeID = tblPersonnelAction.EmployeeID
WHERE (((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FT",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear]) AND
((Year(DateAdd("m",6,[PositionEndDate])))>=[Forms]![frmReports]![FiscalYear])
AND
((Year(DateAdd("m",6,[PositionStartDate])))<=[Forms]![frmReports]![FiscalYear]))
OR (((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"PD",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear]) AND
((Year(DateAdd("m",6,[PositionEndDate])))>=[Forms]![frmReports]![FiscalYear])
AND
((Year(DateAdd("m",6,[PositionStartDate])))<=[Forms]![frmReports]![FiscalYear]))
OR (((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FN",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear]) AND
((Year(DateAdd("m",6,[PositionEndDate])))>=[Forms]![frmReports]![FiscalYear])
AND
((Year(DateAdd("m",6,[PositionStartDate])))<=[Forms]![frmReports]![FiscalYear]))
OR (((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FX",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear]) AND
((Year(DateAdd("m",6,[PositionEndDate])))>=[Forms]![frmReports]![FiscalYear])
AND
((Year(DateAdd("m",6,[PositionStartDate])))<=[Forms]![frmReports]![FiscalYear]))
OR (((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"AP",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear]) AND
((Year(DateAdd("m",6,[PositionEndDate])))>=[Forms]![frmReports]![FiscalYear])
AND
((Year(DateAdd("m",6,[PositionStartDate])))<=[Forms]![frmReports]![FiscalYear]))
ORDER BY tblPersonnel.Department;

programmingrookie said:
Below is the complete SQL statement. What I envision is being able to select
a FY on a form, and pull positions that were active on or before that FY but
did not end until after the FY. Our FY is July - June.

SELECT tblPersonnel.Department, tblPositions.PositionType,
tblPersonnel.LastName, tblPersonnel.FirstName, tblPersonnel.CurrentAssignNo,
tblPositions.OracleTitle, tblPositions.FTE, tblPositions.ApptLength,
tblPersonnelAction.ActionType, tblPersonnelAction.ActionDate,
tblPersonnelAction.ActionValue, tblSalComponents.FiscalYear,
tblSalComponents.BaseAfterRaise, tblSalComponents.EnhancementAR,
tblSalComponents.ProvostInitiativeAR, tblSalComponents.SpecInitMinHireAR,
tblSalComponents.AdminSuppSummer, tblSalComponents.AdminSuppAR,
tblSalComponents.AdminStipAR, tblSalComponents.Professorship,
tblSalComponents.TotalBeforeLongevity, tblPositions.PositionStartDate,
tblPositions.PositionEndDate,
Year([PositionEndDate])-IIf([PositionEndDate]<DateSerial(Year([PositionEndDate]),7,1),1,0)+1
AS FYearEnd,
Year([PositionStartDate])-IIf([PositionStartDate]<DateSerial(Year([PositionStartDate]),7,1),1,0)+1 AS FYearStart
FROM ((tblPersonnel INNER JOIN tblPositions ON tblPersonnel.EmployeeID =
tblPositions.EmployeeID) INNER JOIN tblSalComponents ON
tblPersonnel.EmployeeID = tblSalComponents.EmployeeID) INNER JOIN
tblPersonnelAction ON tblPersonnel.EmployeeID = tblPersonnelAction.EmployeeID
WHERE (((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FT",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear]) AND
((Year([PositionEndDate])-IIf([PositionEndDate]<DateSerial(Year([PositionEndDate]),7,1),1,0)+1)>=[Forms]![frmReports]![FiscalYear])
AND
((Year([PositionStartDate])-IIf([PositionStartDate]<DateSerial(Year([PositionStartDate]),7,1),1,0)+1)<=[Forms]![frmReports]![FiscalYear]))
OR (((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FT",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"PD",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"PD",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FN",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FN",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FX",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"FX",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"AP",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear])) OR
(((tblPersonnel.Department) Like IIf([Forms]![frmReports]![Combo14] Is
Null,"*",[Forms]![frmReports]![Combo14])) AND ((tblPositions.PositionType)
Like IIf([Forms]![frmReports]![PositionType] Is
Null,"AP",[Forms]![frmReports]![PositionType])) AND
((tblPersonnelAction.ActionType)="hired") AND
((tblSalComponents.FiscalYear)=[Forms]![frmReports]![FiscalYear]))
ORDER BY tblPersonnel.Department;


KARL DEWEY said:
year of the start date, and greater than the fiscal year of the end date.
You also did not say what your fiscal year is (everyone does not use the same
fiscal year).
I can not follow your criteria statement. Which records below would you
want to pull?
Record Start Date End Date
1 2006 2006
2 2006 2007
3 2006 2008
4 2006 2009
5 2006 2010
6 2007 2007
7 2007 2008
8 2007 2009
9 2007 2010
10 2008 2006
11 2008 2007
12 2008 2008
13 2008 2009
14 2008 2010
15 2009 2007
16 2009 2008
17 2009 2009
18 2009 2010
19 2010 2006
20 2010 2007
21 2010 2008
22 2010 2009
23 2010 2010
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query current fiscal year 2
Fiscal year 3
Current fiscal year 8
select dates for last fiscal year 3
Fiscal Month Query 4
New column to get fiscal year from existing dates 8
select fiscal year on a query 1
Dates to reflect Fiscal month 2

Top