IIf question

[Mike Hughes]

This is a working query. What I would like to do is display a Y or N in these fields [NOLDBA_INT_ADDRESS_HISTORY.DATE_END, NOLDBA_INT_EMPLOYMENT_HISTORY.DATE_END] depending on if the field is null (12/31/9999). If the field is 12/31/99 I want the query to enter a Y in that field and if the field is not 12/31/9999 I want the query to enter an N in that field.
Can this be done? And can someone please change this query to show me what I need to do in this query?


SELECT DISTINCT NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE AS DO, NOLDBA_INT_CASE_STATUS.WORKER_ID AS WORKER, NOLDBA_INT_CASE_STATUS.CASE_ID AS [CASE], Sum(Now()-NOLDBA_CASE_ROLLUP.DT_LAST_PAY) AS [DAYS SINCE PAY], Sum(NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED-NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID) AS ARREARS, NOLDBA_INT_CASE_MEMBER.MEMBER_ID AS MEMB, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_FIRST AS FN, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_LAST AS LN, NOLDBA_INT_ADDRESS_HISTORY.DATE_END, NOLDBA_INT_EMPLOYMENT_HISTORY.DATE_END
FROM ((((NOLDBA_INT_CASE_STATUS INNER JOIN NOLDBA_CASE_ROLLUP ON NOLDBA_INT_CASE_STATUS.CASE_ID = NOLDBA_CASE_ROLLUP.ID_CASE) INNER JOIN NOLDBA_INT_CASE_MEMBER ON NOLDBA_INT_CASE_STATUS.CASE_ID = NOLDBA_INT_CASE_MEMBER.CASE_ID) INNER JOIN NOLDBA_INT_MEMBER_DEMOGRAPHIC ON NOLDBA_INT_CASE_MEMBER.MEMBER_ID = NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID) INNER JOIN NOLDBA_INT_ADDRESS_HISTORY ON NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID = NOLDBA_INT_ADDRESS_HISTORY.MEMBER_ID) INNER JOIN NOLDBA_INT_EMPLOYMENT_HISTORY ON NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID = NOLDBA_INT_EMPLOYMENT_HISTORY.MEMBER_ID
WHERE (((NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE)=[DISTRICT OFFICE]) AND ((NOLDBA_INT_CASE_STATUS.CASE_STATUS)="O") AND ((NOLDBA_CASE_ROLLUP.DT_LAST_PAY)<Now()-45))
GROUP BY NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE, NOLDBA_INT_CASE_STATUS.WORKER_ID, NOLDBA_INT_CASE_STATUS.CASE_ID, NOLDBA_INT_CASE_MEMBER.MEMBER_ID, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_FIRST, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_LAST, NOLDBA_CASE_ROLLUP.DT_LAST_PAY, NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED, NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID, NOLDBA_INT_CASE_MEMBER.RELATION_CODE, NOLDBA_INT_ADDRESS_HISTORY.ADDR_TYPE, NOLDBA_INT_ADDRESS_HISTORY.DATE_END, NOLDBA_INT_EMPLOYMENT_HISTORY.EMPLOYER_ID, NOLDBA_INT_EMPLOYMENT_HISTORY.DATE_END
HAVING (((NOLDBA_INT_CASE_MEMBER.RELATION_CODE)="A") AND ((NOLDBA_INT_ADDRESS_HISTORY.ADDR_TYPE)="M") AND ((NOLDBA_INT_ADDRESS_HISTORY.DATE_END)=#12/31/9999#) AND ((NOLDBA_INT_EMPLOYMENT_HISTORY.DATE_END)=#12/31/9999#))
ORDER BY NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE, NOLDBA_INT_CASE_STATUS.WORKER_ID;

 

[namliam]

This is a working query. What I would like to do is display a Y or N in these fields [NOLDBA_INT_ADDRESS_HISTORY.DATE_END, NOLDBA_INT_EMPLOYMENT_HISTORY.DATE_END] depending on if the field is null (12/31/9999). If the field is 12/31/99 I want the query to enter a Y in that field and if the field is not 12/31/9999 I want the query to enter an N in that field.
Can this be done?

Yes and Yes you use IIF

And can someone please change this query to show me what I need to do in this query?

No, you will go read the IIF help and/or post properly formatted SQL.

for iif...
IIF(YOURFIELD = YOURVALUE, TRUE, FALSE)