Solved

ms access syntax error, missing operator

Posted on 2011-03-01
3
399 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Lulu0
3 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 35008661
"What am i doing wrong?"

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

0
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 500 total points
ID: 35009194
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
 
LVL 4

Expert Comment

by:MeLindaJohnson
ID: 35009236
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question