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

LUIS FREUNDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
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.
Doug BishopDatabase DeveloperCommented: 
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Doug BishopDatabase DeveloperCommented:
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 FREUNDAuthor Commented:
Thank you guys VERY much!   Solutions are amazing!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.