Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ms access syntax error, missing operator

Posted on 2011-03-01
3
Medium Priority
?
418 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

581 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