Link to home
Start Free TrialLog in
Avatar of LUIS FREUND
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],[REC_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],[REC_DATE]) AS LEAD_TIME_IN_WKS, DateDiff("d",[PLN_DEL_DATE],[REC_DATE]) AS DAYS_LATE, IIf(DateDiff("d",[CONF_DEL_DATE],[REC_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)<>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;
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Just wrap it inside one more IIF
IIF(Len([CONF_DEL_DATE])>0; IIf(DateDiff("d",[CONF_DEL_DATE],[REC_DATE])<=0,"EARLY","LATE") ;IIf(DateDiff("d",[PLN_DEL_DATE],[REC_DATE])<=0,"EARLY","LATE")  ) 

Open in new window

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#)
)

Open in new window

@John

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 blank
By 'blank', do you mean Null?  Is this a datetime field or a string field?
Avatar of LUIS FREUND
LUIS FREUND

ASKER

Datetime field......yes null.
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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]

Open in new window

Thank you very much Gents!   You guys are AMAZING!