Avatar of LUIS FREUND
LUIS FREUND
 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

Microsoft AccessSQL

Avatar of undefined
Last Comment
LUIS FREUND

8/22/2022 - Mon
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy