LUIS FREUND
asked on
EARLY or LATE based on two fields in Accsess.
I've asked this question before and it was solved but wanted to add another criteria to the same question. 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 .
This was theanswer:
ON-TIME: IIf(DateDiff("d",[CONF_DEL _DATE],[RE C_DATE])<= 0,"EARLY", "LATE")
My question now is can "EARLY" "LATE" be done against two fields. Basically REC_DATE first looks at CONF_DEL_DATE and tells me if it's either EARLY or LATE but if it's blank then look at the date of PLN_DEL_DATE to tell me if it's EARLY or LATE. Can this be done? See Below SQL Statement......
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.REC_NO, 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_ALL.CONF_DEL_DATE, tbl_PO_REC.REC_DATE, DateDiff("w",[ORD_DATE],[R EC_DATE]) AS LEAD_TIME_IN_WKS, DateDiff("d",[PLN_DEL_DATE ],[REC_DAT E]) AS DAYS_LATE, IIf(DateDiff("d",[CONF_DEL _DATE],[RE C_DATE])<= 0,"EARLY", "LATE") AS [ON-TIME]
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)<>59 944 And (tbl_PO_REC.SUPP_NO)<>1610 6 And (tbl_PO_REC.SUPP_NO)<>2527 8 And (tbl_PO_REC.SUPP_NO)<>2700 1 And (tbl_PO_REC.SUPP_NO)<>3660 7 And (tbl_PO_REC.SUPP_NO)<>5866 3 And (tbl_PO_REC.SUPP_NO)<>9201 7) 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;
This was theanswer:
ON-TIME: IIf(DateDiff("d",[CONF_DEL
My question now is can "EARLY" "LATE" be done against two fields. Basically REC_DATE first looks at CONF_DEL_DATE and tells me if it's either EARLY or LATE but if it's blank then look at the date of PLN_DEL_DATE to tell me if it's EARLY or LATE. Can this be done? See Below SQL Statement......
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.REC_NO, 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_ALL.CONF_DEL_DATE, tbl_PO_REC.REC_DATE, DateDiff("w",[ORD_DATE],[R
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)<>59
ORDER BY tbl_PO_REC.REC_DATE DESC;
The Where clause could use a tweak, too.
WHERE (
tbl_PO_REC.SUPP_NO Not In(16106, 25278, 27001, 36607, 58663, 59944, 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#)
)
@John
Did you mean to use semi-colons in your expression?
Did you mean to use semi-colons in your expression?
@aikimark...i missed the commas...due to my regional settings i use semi-colons...good catch..
@Luis
if it's blankBy 'blank', do you mean Null? Is this a datetime field or a string field?
ASKER
Datetime field......yes null.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Since there is only a two-column choice, you can replace the Choose() function with another IIF()
IIf(DateDiff("d",
IIf(IsNull([CONF_DEL_DATE]), [PLN_DEL_DATE], [CONF_DEL_DATE]),
[REC_DATE])<=0,"EARLY","LATE") AS [ON-TIME]
ASKER
Thank you very much Gents! You guys are AMAZING!
Open in new window