SELECT EMPLOYEE.[Personnel ID], EMPLOYEE.[Identity Number] AS PID_SELECT, EMPLOYEE.[Thai First Name] AS PID_SELECT_FNAME, ' ' AS PID_SELECT_MNAME, EMPLOYEE.[Thai Last Name] AS PID_SELECT_LNAME, IIf([EMPLOYEE].[Sex]='M','1','2') AS PID_SELECT_SEX, Right(EMPLOYEE.[Birth Date],2)+'/'+Mid(EMPLOYEE.[Birth Date],5,2)+'/'+Left(EMPLOYEE.[Birth Date],4) AS PID_SELECT_BIRTHDATE, EMPLOYEE.Nationality AS PID_SELECT_NATION, iif(EMPLOYEE.[Religious denomination]='ط','1',iif(EMPLOYEE.[Religious denomination]='ʵ','2',iif(EMPLOYEE.[Religious denomination]='','3','7'))) AS PID_SELECT_RELIGION, MASTER.[Employee Date] AS WORK_START_DATE, '112' AS POSITION_REGISTER_ID, '0' AS RELATION, EMPLOYEE.[Identity Number] AS PID, EMPLOYEE.[Thai First Name] AS PID_FNAME, ' ' AS PID_MNAME, EMPLOYEE.[Thai Last Name] AS PID_LNAME, IIf([EMPLOYEE].[Sex]='M','1','2') AS PID_SEX, Right([EMPLOYEE].[Birth Date],2)+'/'+Mid([EMPLOYEE].[Birth Date],5,2)+'/'+Left([EMPLOYEE].[Birth Date],4) AS PID_BIRTHDAY, EMPLOYEE.Nationality AS PID_NATION, iif(EMPLOYEE.[Religious denomination]='ط','1',iif(EMPLOYEE.[Religious denomination]='ʵ','2',iif(EMPLOYEE.[Religious denomination]='','3','7'))) AS PID_RELIGION, ' ' AS Child_Incompletent, ' ' AS Child_Regularity
FROM EMPLOYEE INNER JOIN MASTER ON EMPLOYEE.[Personnel ID]=MASTER.[Personnel ID]
WHERE MASTER.[Employee Date] >= (SELECT [Criteria].[StartDate] FROM [CRITERIA] WHERE  [Criteria].[Field]='START_DATE')  and  MASTER.[Employee Date] <= (SELECT [Criteria].[EndDate] FROM [CRITERIA] WHERE  [Criteria].[Field]='START_DATE') AND ((MASTER.[Terminate Date]) Is Null);
