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!

Recommended Answers

All 4 Replies

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..

Just to test try using.

iif() or Switch() functions.

it works now!
thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.