SELECT PERSONAL.[Personnel ID], PERSONAL.[Identity Number] AS PID_SELECT, PERSONAL.[Thai First Name] AS PID_SELECT_FNAME, ' ' AS PID_SELECT_MNAME, PERSONAL.[Thai Last Name] AS PID_SELECT_LNAME, IIf([PERSONAL].[Sex]='M','1','2') AS PID_SELECT_SEX, Right(PERSONAL.[Birth Date],2)+'/'+Mid(PERSONAL.[Birth Date],5,2)+'/'+Left(PERSONAL.[Birth Date],4) AS PID_SELECT_BIRTHDATE, PERSONAL.Nationality AS PID_SELECT_NATION, IIf(PERSONAL.[Religious denomination]='ط','1',IIf(PERSONAL.[Religious denomination]='ʵ','2',IIf(PERSONAL.[Religious denomination]='','3','7'))) AS PID_SELECT_RELIGION, MASTER.[Employee Date] AS WORK_START_DATE, '111' AS POSITION_REGISTER_ID, '0' AS RELATION, PERSONAL.[Identity Number] AS PID, PERSONAL.[Thai First Name] AS PID_FNAME, ' ' AS PID_MNAME, PERSONAL.[Thai Last Name] AS PID_LNAME, IIf([PERSONAL].[Sex]='M','1','2') AS PID_SEX, Right([PERSONAL].[Birth Date],2)+'/'+Mid([PERSONAL].[Birth Date],5,2)+'/'+Left([PERSONAL].[Birth Date],4) AS PID_BIRTHDAY, PERSONAL.Nationality AS PID_NATION, IIf(PERSONAL.[Religious denomination]='ط','1',IIf(PERSONAL.[Religious denomination]='ʵ','2',IIf(PERSONAL.[Religious denomination]='','3','7'))) AS PID_RELIGION, ' ' AS Child_Incompletent, ' ' AS Child_Regularity, MASTER.[Terminate Date]
FROM PERSONAL INNER JOIN MASTER ON PERSONAL.[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) );
