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
Lulu0Asked:
Who is Participating?
 
pteranodon72Connect With a Mentor Commented:
It is time to move this logic into a function. Have you written VBA functions before? They will let you:
 - use whitespace to organize the decision tree
 - add comments to document what is going on
 - debug code line by line, rather than all or nothing, like the query editor
 - reuse tested pieces of code
 - improve readibility tremendously

pT72
0
 
peter57rCommented:
"What am i doing wrong?"

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

0
 
MeLindaJohnsonCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.