Update Command Error (1 Viewer)

[balvinder]

Hi,

I'am using below update command to update existing record on the basis of primary key (Instrument_Number) but getting below error
"Syntax Error (Missing Operator).

Can anyone help me to find out the error.


DoCmd.RunSQL "Update Telecalling_database set Calling_end_Date_time = " & Me.timer1.Value & ", Paid_Unpaid_Status = " & Me.Paid_unpaid_status.Value & ", No_of_Premium_Required = " & Me.No_of_Prem_required.Value & ", Calling_Code= " & Me.Calling_code.Value & ", Customer_comments= " & Me.Customer_comments.Value & ", GO_CODE_Ingenium= " & Me.GO_Code.Value & ", Modal_Premium= " & Me.Modal_premium.Value & ", Policy_Paid_To_Date= " & Me.Policy_paid_to_date.Value & ",MFYP_FRYP= " & Me.MFYP_FRYP.Value & ", Frequency= " & Me.Frequency.Value & ", Account_Holder_Name= " & Me.ACt_Holder_Name.Value & ", Client_Name= " & Me.Client_Name.Value & ", Account_Number= " & Me.Account_Number.Value & ", Bank_Name_Ingenium= " & Me.Bank_Name_Ingenium.Value & " WHERE Instrument_Number = " & Me.Instrument_Number.Value & ""


Many Thanks !!!

 

[Jeanette Cunningham]

Here is a method that lets you find the errors.

Dim strSQL As String
strSQL = "Update Telecalling_database set Calling_end_Date_time = " & Me.timer1.Value & ", Paid_Unpaid_Status = " & Me.Paid_unpaid_status.Value & ", No_of_Premium_Required = " & Me.No_of_Prem_required.Value & ", Calling_Code= " & Me.Calling_code.Value & ", Customer_comments= " & Me.Customer_comments.Value & ", GO_CODE_Ingenium= " & Me.GO_Code.Value & ", Modal_Premium= " & Me.Modal_premium.Value & ", Policy_Paid_To_Date= " & Me.Policy_paid_to_date.Value & ",MFYP_FRYP= " & Me.MFYP_FRYP.Value & ", Frequency= " & Me.Frequency.Value & ", Account_Holder_Name= " & Me.ACt_Holder_Name.Value & ", Client_Name= " & Me.Client_Name.Value & ", Account_Number= " & Me.Account_Number.Value & ", Bank_Name_Ingenium= " & Me.Bank_Name_Ingenium.Value & " WHERE Instrument_Number = " & Me.Instrument_Number.Value & ""

Debug.Print strSQL

DoCmd.RunSQL strSQL


Run the code on the form by clicking the button or whatever causes the code to run.
Open the immediate window (Ctl+G)
Copy the query string from the immediate window to a new query in SQL view.
Switch to datasheet view and the error message will help you locate the error.
Note: you don’t need to put .Value in the sql.
Here’s how to do it without .Value for one variable:
set Calling_end_Date_time = " & Me.timer1 & ",
You can go through the query and remove all the .Value.

 

[balvinder]

Thanks for quick reply & warm help...

Have done exactly same process as suggested by you But not able to find error since everything seems perfectly fine.
Below is the output of immediate window & Error Message i got post converting into datasheet view.

Datasheet convert error :- Syntax Error (Missing Operator) in query expression '6/28/2011 6:03:23 PM'.


Immediate window output: -

UPDATE Telecalling_database set Calling_end_Date_time = 6/28/2011 6:03:23 PM, Paid_Unpaid_Status = , No_of_Premium_Required = 0, Calling_Code= ISSUE, Customer_comments= Customer had issues with services, GO_CODE_Ingenium= X0134, Modal_Premium= 15000, Policy_Paid_To_Date= 6/17/2012,MFYP_FRYP= FRYP/RYP, Frequency= Annual, Account_Holder_Name= ARUN NATHRAO GOLDE, Client_Name= MR. ARUN NATHRAO GOLDE, Account_Number= 003010100490597, Bank_Name_Ingenium= uti bank ltd WHERE Instrument_Number = 5495696

 

[boblarson]

Dates need octothorpes (#) surrounding them and text needs quotes. It would appear that you have not included either in your SQL code.

 

[balvinder]

Millions Thanks to both of you...!!!!
You are really a gem...

Code is working fine now..)

God bless you both

 

[boblarson]