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") & "#)) "
Thanks!
