asked on

Adding words to a field based on value in Access

I have this sql statement in Access and what I wanted to do is add a field that would read "EARLY" if the number is negative and "LATE" if the number is positive based on the "DAYS_LATE" field which is a numerical field . See below......

SELECT DISTINCT tbl_PO_REC.PO, tbl_PO_REC.POS, tbl_PO_REC.SUPP_NO, tbl_PO_REC.SUPP_NAME, tbl_PO_REC.ITEM, tbl_PO_REC.ORD_QTY, tbl_PO_REC.DEL_QTY, tbl_PO_REC.BACK_ORD_QTY, tbl_PO_REC.TOTAL_COST, tbl_PO_ALL.ORD_DATE, tbl_PO_ALL.PLN_DEL_DATE, tbl_PO_REC.REC_DATE, DateDiff("w",[ORD_DATE],[REC_DATE]) AS LEAD_TIME_IN_WKS, DateDiff("d",[PLN_DEL_DATE],[REC_DATE]) AS DAYS_LATE
FROM tbl_PO_REC LEFT JOIN tbl_PO_ALL ON (tbl_PO_REC.POS = tbl_PO_ALL.POS) AND (tbl_PO_REC.PO = tbl_PO_ALL.PO)
WHERE (((tbl_PO_REC.SUPP_NO)<>59944 And (tbl_PO_REC.SUPP_NO)<>16106 And (tbl_PO_REC.SUPP_NO)<>25278 And (tbl_PO_REC.SUPP_NO)<>27001 And (tbl_PO_REC.SUPP_NO)<>36607 And (tbl_PO_REC.SUPP_NO)<>58663 And (tbl_PO_REC.SUPP_NO)<>92017) AND ((tbl_PO_REC.ITEM)<>"COST ITEM") AND ((tbl_PO_REC.REC_DATE) Is Null Or (tbl_PO_REC.REC_DATE)>#12/31/2018#))
ORDER BY tbl_PO_REC.REC_DATE DESC;

Open in new window

Fabrice Lambert

Either:
Add the Following to your query:

iif([DAYS_LATE]>=0, "EARLY", "LATE")

Open in new window

Or write a data macro that will populate the desired field, and leave the query alone.

ASKER CERTIFIED SOLUTION

D B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

Or, get a but fancier and have it display EARLY, LATE or ON-TIME"

SELECT DISTINCT tbl_PO_REC.PO, tbl_PO_REC.POS, tbl_PO_REC.SUPP_NO, tbl_PO_REC.SUPP_NAME, 
                tbl_PO_REC.ITEM, tbl_PO_REC.ORD_QTY, tbl_PO_REC.DEL_QTY, tbl_PO_REC.BACK_ORD_QTY, 
                tbl_PO_REC.TOTAL_COST, tbl_PO_ALL.ORD_DATE, tbl_PO_ALL.PLN_DEL_DATE, tbl_PO_REC.REC_DATE, 
                DATEDIFF("w",[ORD_DATE],[REC_DATE]) AS LEAD_TIME_IN_WKS, DATEDIFF("d",[PLN_DEL_DATE],[REC_DATE]) AS DAYS_LATE,
                IIF(DATEDIFF("d",[PLN_DEL_DATE],[REC_DATE]) < 0, "EARLY", IIF(DATEDIFF("d",[PLN_DEL_DATE],[REC_DATE]) = 0 "ON-TIME", "LATE")
FROM tbl_PO_REC 
LEFT JOIN tbl_PO_ALL 
ON (tbl_PO_REC.POS = tbl_PO_ALL.POS) 
AND (tbl_PO_REC.PO = tbl_PO_ALL.PO)
WHERE (((tbl_PO_REC.SUPP_NO)<>59944 
	AND (tbl_PO_REC.SUPP_NO)<>16106 
	AND (tbl_PO_REC.SUPP_NO)<>25278 
	AND (tbl_PO_REC.SUPP_NO)<>27001 
	AND (tbl_PO_REC.SUPP_NO)<>36607 
	AND (tbl_PO_REC.SUPP_NO)<>58663 
	AND (tbl_PO_REC.SUPP_NO)<>92017) 
	AND ((tbl_PO_REC.ITEM)<>"COST ITEM") 
	AND ((tbl_PO_REC.REC_DATE) IS NULL OR (tbl_PO_REC.REC_DATE)>#12/31/2018#))
ORDER BY tbl_PO_REC.REC_DATE DESC;

Open in new window

LUIS FREUND

ASKER

Thank you guys VERY much! Solutions are amazing!