Return yes or no based on null field

[Mike Hughes]

I'm trying to have my query return either Yes or No based on a date field. If the field contains the date 12/31/9999 I want the field to return as yes, not the date and if it doesn't have 12/31/9999 I want the field to return as no.

Can someone show me how? Here is the query

SELECT
NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE AS DO,
NOLDBA_INT_CASE_STATUS.WORKER_ID AS WORKER,
NOLDBA_INT_CASE_STATUS.CASE_STATUS AS STATUS,
NOLDBA_INT_CASE_STATUS.CASE_ID AS [CASE],
Sum(NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED-NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID) AS ARREARS,
Sum(Date()-NOLDBA_CASE_ROLLUP.DT_LAST_PAY) AS [DAYS SINCE PAY],
NOLDBA_INT_CASE_MEMBER.MEMBER_ID AS MEMB, =NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_FIRST & ", " & NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_LAST AS NAME, NOLDBA_INT_EMPLOYMENT_HISTORY_1.DATE_END AS EMP,
NOLDBA_INT_ADDRESS_HISTORY.DATE_END AS [ADD] INTO [TABLE 5]

FROM ((((((NOLDBA_INT_CASE_STATUS 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_REF_CASE_WORKER ON NOLDBA_INT_CASE_STATUS.WORKER_ID=NOLDBA_REF_CASE_WORKER.WORKER_ID) INNER JOIN NOLDBA_CASE_ROLLUP ON NOLDBA_INT_CASE_STATUS.CASE_ID=NOLDBA_CASE_ROLLUP.ID_CASE) LEFT JOIN NOLDBA_INT_EMPLOYMENT_HISTORY ON NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID=NOLDBA_INT_EMPLOYMENT_HISTORY.MEMBER_ID) LEFT JOIN NOLDBA_INT_EMPLOYMENT_HISTORY AS NOLDBA_INT_EMPLOYMENT_HISTORY_1 ON NOLDBA_INT_CASE_MEMBER.MEMBER_ID=NOLDBA_INT_EMPLOYMENT_HISTORY_1.MEMBER_ID) LEFT JOIN NOLDBA_INT_ADDRESS_HISTORY ON NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID=NOLDBA_INT_ADDRESS_HISTORY.MEMBER_ID

WHERE
NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE In ([DISTRICT]) And
NOLDBA_INT_CASE_STATUS.WORKER_ID In ("MONDA","MONDB","MONDC","MONDD","MONDE","MONDF") And
NOLDBA_CASE_ROLLUP.DT_LAST_PAY<Date()-40 And
NOLDBA_INT_CASE_MEMBER.RELATION_CODE="A" And
NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED-NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID>0

GROUP BY NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE, NOLDBA_INT_CASE_STATUS.WORKER_ID, NOLDBA_INT_CASE_STATUS.CASE_STATUS, NOLDBA_INT_CASE_STATUS.CASE_ID, NOLDBA_INT_CASE_MEMBER.MEMBER_ID, NOLDBA_INT_EMPLOYMENT_HISTORY_1.DATE_END, NOLDBA_INT_ADDRESS_HISTORY.DATE_END, NOLDBA_CASE_ROLLUP.DT_LAST_PAY, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_FIRST, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_LAST, NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED, NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID, NOLDBA_INT_ADDRESS_HISTORY.ADDR_TYPE

HAVING
(((NOLDBA_INT_ADDRESS_HISTORY.ADDR_TYPE)="M"))

 

[DCrake]

Alias:Iif([DateField]>1,"Yes","No")

 

[Mike Hughes]

Good Man

That worked, THANKS SO MUCH.

mIKE

 

[Mike Hughes]

I spoke to quickly
I tried the same change for another date field and everything gets returned as yes when there should be one no.
Here is what I have, what did I do wrong?

SELECT
NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE AS DO,
NOLDBA_INT_CASE_STATUS.WORKER_ID AS WORKER,
NOLDBA_INT_CASE_STATUS.CASE_STATUS AS STATUS,
NOLDBA_INT_CASE_STATUS.CASE_ID AS [CASE],
Sum(NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED-NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID) AS ARREARS,
Sum(Date()-NOLDBA_CASE_ROLLUP.DT_LAST_PAY) AS [DAYS SINCE PAY],
NOLDBA_INT_CASE_MEMBER.MEMBER_ID AS MEMB, =NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_FIRST & " " & NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_LAST AS NAME, NOLDBA_INT_ADDRESS_HISTORY.DATE_END,
IIf([NOLDBA_INT_EMPLOYMENT_HISTORY_1.DATE_END]>1,"Yes","No") AS EMP,
IIf([NOLDBA_INT_ADDRESS_HISTORY.DATE_END]>1,"Yes","No") AS ADD INTO [TABLE 5]

FROM
((((((NOLDBA_INT_CASE_STATUS 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_REF_CASE_WORKER ON NOLDBA_INT_CASE_STATUS.WORKER_ID = NOLDBA_REF_CASE_WORKER.WORKER_ID) INNER JOIN NOLDBA_CASE_ROLLUP ON NOLDBA_INT_CASE_STATUS.CASE_ID = NOLDBA_CASE_ROLLUP.ID_CASE) LEFT JOIN NOLDBA_INT_EMPLOYMENT_HISTORY ON NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID = NOLDBA_INT_EMPLOYMENT_HISTORY.MEMBER_ID) LEFT JOIN NOLDBA_INT_EMPLOYMENT_HISTORY AS NOLDBA_INT_EMPLOYMENT_HISTORY_1 ON NOLDBA_INT_CASE_MEMBER.MEMBER_ID = NOLDBA_INT_EMPLOYMENT_HISTORY_1.MEMBER_ID) LEFT JOIN NOLDBA_INT_ADDRESS_HISTORY ON NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID = NOLDBA_INT_ADDRESS_HISTORY.MEMBER_ID

WHERE
NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE In ([DISTRICT]) AND
NOLDBA_INT_CASE_STATUS.WORKER_ID In ("MONDA","MONDB","MONDC","MONDD","MONDE","MONDF") AND
NOLDBA_CASE_ROLLUP.DT_LAST_PAY<Date()-40 AND
NOLDBA_INT_CASE_MEMBER.RELATION_CODE="A"
AND [NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_OWED]-[NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_PAID]>0 AND
NOLDBA_INT_ADDRESS_HISTORY.ADDR_TYPE="M"

GROUP BY NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE, NOLDBA_INT_CASE_STATUS.WORKER_ID, NOLDBA_INT_CASE_STATUS.CASE_STATUS, NOLDBA_INT_CASE_STATUS.CASE_ID, NOLDBA_INT_CASE_MEMBER.MEMBER_ID, NOLDBA_INT_EMPLOYMENT_HISTORY_1.DATE_END, NOLDBA_INT_ADDRESS_HISTORY.DATE_END, NOLDBA_CASE_ROLLUP.DT_LAST_PAY, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_FIRST, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_LAST, NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED, NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID, NOLDBA_INT_ADDRESS_HISTORY.ADDR_TYPE


ORDER BY NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE, NOLDBA_INT_CASE_STATUS.WORKER_ID, NOLDBA_INT_CASE_STATUS.CASE_ID, NOLDBA_CASE_ROLLUP.DT_LAST_PAY, NOLDBA_INT_CASE_MEMBER.MEMBER_ID, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_FIRST, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_LAST;

 

[DCrake]

Create a simple query
based on the IIF() logic just using the fields in question to test first.

 

[Mike Hughes]

I just tried a simple query with the same wrong results..........

 

[boblarson]

Are you absolutely sure that it is not returning one no? Put NO in the criteria of that calculated field and see if anything shows up. I've had it before that I had large amounts of data and overlooked it.

 

[Mike Hughes]

That didn't work either. Let me explain more, If the NOLDBA_INT_EMPLOYMENT_HISTORY_1.DATE_END is 12/31/9999 (which indicates that the end date field is empty, no date in it) the answer should be returned as "YES" if there is a date other that 12/31/9999 the answer would be no
if the NOLDBA_INT_ADDRESS_HISTORY.DATE_END is 12/31/9999 (which indicates that the end date field is empty, no date in it) the answer should be returned as "YES" if there is a date other that 12/31/9999 the answer would be no. I don't know if that makes a difference or not.

 

[boblarson]

Ah then this should do it:

Alias:Iif([DateField]=#12/31/9999#,"Yes","No")

 

[Mike Hughes]

That did it. Sorry I didn't give you all the information you needed the first time.

Thanks again Mike

 

[boblarson]

Sorry I didn't give you all the information you needed the first time.

Actually you did give the information. As for David's original reply, I think he was thinking 12/31/1899 which is day 0, so that's why he gave the original >0 reply.