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;
Either:
Add the Following to your query:
Add the Following to your query:
iif([DAYS_LATE]>=0, "EARLY", "LATE")
Database Developer
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;