Adding words to a field based on value in Access

LUIS FREUND
LUIS FREUND used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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.
Database Developer
Commented: 
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", "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

Doug BishopDatabase Developer

Commented:
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

Author

Commented:
Thank you guys VERY much!   Solutions are amazing!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial