Syntax Error

[QVeen72]

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

[debasisdas]

It is very difficult to suggest anything without your table structure.

[debasisdas]

Try to use CASE instead of IF

That is supported in SQL.

[towsie]

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

[QVeen72]

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

[debasisdas]

What is that IF in the SQL syntax ?

[towsie]

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