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

Last Comment

LUIS FREUND

8/22/2022 - Mon

Fabrice Lambert

2/7/2019

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

2/7/2019

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

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

2/7/2019

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

2/8/2019

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