Solved

ms access syntax error, missing operator

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now