Syntax Error

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • towsie
    New Member
    • Jun 2007
    • 28
    #1

    Syntax Error

    Hi,

    I would be very grateful if someone could have a look at the staement below and help me find the Syntax Error.
    [code=vb]
    ("Select origin_of_sale as Origin, Category, Crude.F3 as Customer, sales_terms, autolift, load_point as lp, Crude.F11 as Vessel, Crude.F2 as CONTRACT_NO, VALUE_ONLY, invoice_unit, Ledger_unit, Contract_date, template_name, acct_to_group_c onversion as conv, Crude.F5, Crude.F6 as quantity, Crude.F8 as tax_quantity, Crude.F1 as participator, Crude.F14 as invoice_no, Crude.F9 as sales_date, Crude.F10 as pay_date, Crude.F12 as header, Crude.F4 as shipping, Crude.F7 as price, Crude.F13 as export " & _

    "From Crude inner join t_sales_templat e on Crude.F5 = t_sales_templat e.origin_of_sal e " & _

    "Where (If(Customer = 'Shell Uk Ltd' AND Participator = 'SHELL UK LIMITED' Then template_name = 'Crude - SUKO - ' & Crude.F5) " & _
    "Else " & _
    "(If(Custom er = 'A/B Svenska Shell' OR 'SHELL U.K. EXPLORATION & PRODUCTION' OR 'Shell Canada Ltd' OR 'Shell Canada Products' OR 'Shell Chemicals (Uk) Ltd.' OR 'Shell Chemicals Europe BV' OR 'Shell Deutschland Oil GMBH' OR 'Shell Expro UK Sector 11 (EOEL)' OR 'Shell Expro UK Sector 11 (EOP)' OR 'Shell Gas Direct' OR 'Shell Gas Limited' OR 'Shell Nederland Raffinaderij B.V.' OR 'Shell Trading Canada' OR 'Shell Trading International Limited' OR 'Shell Trading US Company' OR 'Shell Western Supply and Trading Limited' OR 'Societe des Petroles Shell' OR (Customer = 'Shell UK Ltd' AND Participator NOT 'SHELL UK LIMITED') Then template_name = 'Crude - Group - ' & Crude.F5)) " & _
    "Else " & _
    "(template_ name = 'Crude - Third Party - ' & Crude.F5);")
    [/code]
    Thanks

    Towsie
    Last edited by debasisdas; Feb 19 '08, 11:40 AM. Reason: added code=vb tags
    Tags: None
    • debasisdas
      Recognized Expert Expert
      • Dec 2006
      • 8119
      #2
      What is that IF in the SQL syntax ?

        Comment

        • QVeen72
          Recognized Expert Top Contributor
          • Oct 2006
          • 1445
          #3
          Hi,

          I dont think you can use If .. Then.. Else.. End IF in an SQL Statement like that. If you are using Access, you can use
          IIF(EvaluateCon dition, True_do_This, False_do_this)

          Change your statement accordingly..

          Regards
          Veena

            Comment

            • towsie
              New Member
              • Jun 2007
              • 28
              #4
              Hi,

              I have changes the statement to
              [code=vb]
              ("Select origin_of_sale as Origin, Category, Crude.F3 as Customer, sales_terms, autolift, load_point as lp, Crude.F11 as Vessel, Crude.F2 as CONTRACT_NO, VALUE_ONLY, invoice_unit, Ledger_unit, Contract_date, template_name, acct_to_group_c onversion as conv, Crude.F5, Crude.F6 as quantity, Crude.F8 as tax_quantity, Crude.F1 as participator, Crude.F14 as invoice_no, Crude.F9 as sales_date, Crude.F10 as pay_date, Crude.F12 as header, Crude.F4 as shipping, Crude.F7 as price, Crude.F13 as export " & _

              "From Crude inner join t_sales_templat e on Crude.F5 = t_sales_templat e.origin_of_sal e " & _

              "Where (IIf(Customer = 'Shell Uk Ltd' AND Participator = 'SHELL UK LIMITED'), (template_name = 'Crude - SUKO - ' & Crude.F5), (IIf(Customer = 'A/B Svenska Shell' OR 'SHELL U.K. EXPLORATION & PRODUCTION' OR 'Shell Canada Ltd' OR 'Shell Canada Products' OR 'Shell Chemicals (Uk) Ltd.' OR 'Shell Chemicals Europe BV' OR 'Shell Deutschland Oil GMBH' OR 'Shell Expro UK Sector 11 (EOEL)' OR 'Shell Expro UK Sector 11 (EOP)' OR 'Shell Gas Direct' OR 'Shell Gas Limited' OR 'Shell Nederland Raffinaderij B.V.' OR 'Shell Trading Canada' OR 'Shell Trading International Limited' OR 'Shell Trading US Company' OR 'Shell Western Supply and Trading Limited' OR 'Societe des Petroles Shell' OR (Customer = 'Shell UK Ltd' AND Participator NOT 'SHELL UK LIMITED'))), (template_name = 'Crude - Group - ' & Crude.F5), (template_name = 'Crude - Third Party - ' & Crude.F5);")
              [/code]
              There is still a syntax error.

              Thanks

              Towsie
              Last edited by debasisdas; Feb 19 '08, 11:53 AM. Reason: added code=vb tags

                Comment

                • debasisdas
                  Recognized Expert Expert
                  • Dec 2006
                  • 8119
                  #5
                  Try to use CASE instead of IF

                  That is supported in SQL.

                    Comment

                    • debasisdas
                      Recognized Expert Expert
                      • Dec 2006
                      • 8119
                      #6
                      It is very difficult to suggest anything without your table structure.

                        Comment

                        • QVeen72
                          Recognized Expert Top Contributor
                          • Oct 2006
                          • 1445
                          #7
                          Hi,

                          Look at OR :

                          Customer = 'A/B Svenska Shell' OR 'SHELL U.K. EXPLORATION'

                          It should be:

                          Customer = 'A/B Svenska Shell' OR Customer = 'SHELL U.K. EXPLORATION'

                          Such things are handled better in the Reports.. instead of writing such Complex SQL..

                          More over, IIF Condions has to be used for Field Names not in Where Conditions..


                          REgards
                          Veena

                            Comment

                            Previous template Next
                            Working...