asked on

ms access syntax error, missing operator

I keep getting a "missing operator" error. What am i doing wrong?

iif(t2.MABD_Compliance is null,"Missing Data",((iif(t2.TransitMode="CP" or t2.TransitMode="LTL") and (dateadd("d",-(IIf(t2.TransitMode="TL" Or t2.TransitMode="IM", datediff("d",t2.Carrier_Pickup_Date,(dateadd("d",-2,t2.MABD))),datediff("d",t2.[Ship-On_Date],(dateadd("d",-2,t2.MABD))))), t2.MABD) < (dateadd("d",2,t2.Create_Date))) or iif(t2.TransitMode="TL" or t2.TransitMode="IM") and (datediff("d",t2.Carrier_Pickup_Date,t2.MABD) < (IIf(t2.TransitMode="TL" Or t2.TransitMode="IM", datediff("d",t2.Carrier_Pickup_Date,(dateadd("d",-2,t2.MABD))),datediff("d",t2.[Ship-On_Date],(dateadd("d",-2,t2.MABD)))))),"inaccurateLT","ok")) AS Root_Cause

peter57r

"What am i doing wrong?"

In my view, creating something that is un-maintainable. Best of luck!!

ASKER CERTIFIED SOLUTION

pteranodon72

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MeLindaJohnson

iif
(
inull(t2.MABD_Compliance),
"Missing Data",
iif
(
(
(t2.TransitMode="CP" or t2.TransitMode="LTL") and
dateadd("d",
IIf
(t2.TransitMode="TL" Or t2.TransitMode="IM",
datediff("d",t2.Carrier_Pickup_Date, dateadd("d",-2,t2.MABD)),
datediff("d",t2.[Ship-On_Date],dateadd("d",-2,t2.MABD))
              )*-1,
       t2.MABD
) < dateadd("d",2,t2.Create_Date)
)

or

       (
       iif
(
(t2.TransitMode="TL" or t2.TransitMode="IM") and
       datediff("d",t2.Carrier_Pickup_Date,t2.MABD) < IIf
(
(t2.TransitMode="TL" Or t2.TransitMode="IM"),
                                                  datediff("d",t2.Carrier_Pickup_Date, dateadd("d",-2,t2.MABD)),
                                                  datediff("d",t2.[Ship-On_Date],Dateadd("d",-2,t2.MABD))
),
"inaccurateLT","ok"
)
)
) AS Root_Cause