Solved

ms access syntax error, missing operator

Posted on 2011-03-01
3
398 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.NET return row from SQL to DataSet and get column values ? 3 51
Oracle 10g - Select rows into colums 4 22
SQL Server Update Query Streamline 4 29
Help Required 3 97
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

810 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