Hi, I am running this line of code to insert some data into table. I am using select case on one of the data. When I run it, this error was generated:
"Syntax error (missing operator) in query expression 'CASE dbo_tblFinanceApp.UPADisclosed WHEN 0 THEN 'Undisclosed' WHEN -1 THEN 'Disclosed' END'

I have tried this line of code in T-SQL, and it works. I don't know what's wrong with the select case.

This is the code:

DoCmd.RunSQL "INSERT INTO tblContractAllocation ( FinanceAppID, Company_Name, SettlementDate, ExpiryDate, TotalMonthlyTerm, EX_GST_Monthly, EquipmentDescription, Company_Address, Company_Address2, Company_Suburb, Company_State, Company_Number, Company_Email, Customer_Name, Customer_Surname, Customer_Number, Customer_Mobile, Customer_Email, Status, Description, SupplierName, SupplierType, Disclosed_Status ) " & _
" SELECT dbo_tblFinanceApp.FinanceAppID, dbo_tblCustomers.CustomerName As Company_Name, dbo_tblFinanceApp.SettlementDate, dbo_tblFinanceApp.ExpiryDate, dbo_tblFinanceApp.TotalMonthlyTerm, " & _
            " dbo_tblFinanceApp.PaymentsNet As EX_GST_Monthly, dbo_tblFinanceApp.EquipmentDescription, dbo_tblCustomers.StreetAddress As Company_Address, dbo_tblCustomers.Address2 As Company_Address2, " & _
            " dbo_tblCustomers.Suburb As Company_Suburb, dbo_tblStates.Name As Company_State, dbo_tblCustomers.ContactPhone As Company_Number, dbo_tblCustomers.EmailAddress As Company_Email, " & _
            " dbo_tblCustomerContacts.FirstName As Customer_Name, dbo_tblCustomerContacts.Surname As Customer_Surname, dbo_tblCustomerContacts.PhoneNo As Customer_Number, dbo_tblCustomerContacts.MobileNo As Customer_Mobile, " & _
            " dbo_tblCustomerContacts.EmailAdress As Customer_Email, dbo_tblFinanceAppStatus.Description As Status, dbo_tblFinancePlanType.Description, dbo_tblSuppliers.SupplierName, dbo_tblSupplierTypes.SupplierType, " & _
            " CASE dbo_tblFinanceApp.UPADisclosed WHEN 0 THEN 'Undisclosed' WHEN -1 THEN 'Disclosed' END As Disclosed_Status " & _
" FROM ((((((dbo_tblFinanceApp INNER JOIN dbo_tblCustomers ON dbo_tblFinanceApp.CustomerID = dbo_tblCustomers.CustomerID) INNER JOIN dbo_tblCustomerContacts ON dbo_tblFinanceApp.CustomerID = dbo_tblCustomerContacts.CustomerId) " & _
            " INNER JOIN dbo_tblFinanceAppStatus ON dbo_tblFinanceApp.StatusID = dbo_tblFinanceAppStatus.FAStatusID) INNER JOIN dbo_tblFinancePlanType ON dbo_tblFinanceApp.TypeOfFinancePlan = dbo_tblFinancePlanType.FinancePlanTypeId) " & _
            " INNER JOIN dbo_tblSupplierTypes ON dbo_tblFinanceApp.Source = dbo_tblSupplierTypes.SupplierTypeId) INNER JOIN dbo_tblSuppliers ON dbo_tblFinanceApp.SupplierId = dbo_tblSuppliers.SupplierID) " & _
            " INNER JOIN dbo_tblStates ON dbo_tblFinanceApp.StateId = dbo_tblStates.StateID " & _
" WHERE (((dbo_tblFinanceAppStatus.FAStatusID) in (3, 10, 21)) AND ((dbo_tblFinancePlanType.FinancePlanTypeId) in (1, 6)) AND ((dbo_tblFinanceApp.ExpiryDate) <= #" & Format(Me.ExpiryDate, "mm/dd/yyyy") & "#)) "


Edited by wewehalim: n/a

6 Years
Discussion Span
Last Post by wewehalim

Is it because of the single quote.

Just frame and print the query before executing, it will be easy to trace the error.


The thing is, I have run above code on the query analyser, and it works fine.

I have tried all this code:
CASE dbo_tblFinanceApp.UPADisclosed WHEN 0 THEN 'Undisclosed' WHEN -1 THEN 'Disclosed' END As Disclosed_Status

CASE dbo_tblFinanceApp.UPADisclosed WHEN 0 THEN "Undisclosed" WHEN -1 THEN "Disclosed" END As Disclosed_Status

Nothings works though..

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.