9 Replies - 2921 Views - Last Post: 14 May 2009 - 06:39 PM Rate Topic: -----

#1 Prasadh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 03-May 09

syntax error in update statement

Posted 09 May 2009 - 10:29 PM

Hi all,

i get a error msg as sytax in update query while i execute below is my code

i dont know whether i left any braces or with in code


Else
strsql = ("Update weeklyAR set Analyst = '" & tempnam & "',Baanco = '" & tempnam1 & "',Customer = '" & Trim(xl.ActiveSheet.Range("C" & i)) & "',Total AR = '" & tempnam2 & "',Current = '" & Trim(xl.ActiveSheet.Range("E" & i)) & "',1-5 Days = '" & Trim(xl.ActiveSheet.Range("F" & i)) & "',6-10 Days = '" & Trim(xl.ActiveSheet.Range("G" & i)) & "',11-30 Days = '" & Trim(xl.ActiveSheet.Range("H" & i)) & "',31-60 Days = '" & Trim(xl.ActiveSheet.Range("i" & i)) & "',61+ Days = '" & Trim(xl.ActiveSheet.Range("j" & i)) & "' where id = '" & Trim(xl.ActiveSheet.Range("D" & i)) & "'")
cn.Execute strsql
dupcount = dupcount + 1


kindly anyone assist me.

rgds
Prasadh

Is This A Good Question/Topic? 0
  • +

Replies To: syntax error in update statement

#2 firebolt  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 92
  • View blog
  • Posts: 5,561
  • Joined: 20-February 09

Re: syntax error in update statement

Posted 10 May 2009 - 12:49 AM

please post your code in the code tags. Like this :code: Thanks
Was This Post Helpful? 0
  • +
  • -

#3 Nikhil_07n  Icon User is offline

  • The cheese stands alone..
  • member icon

Reputation: 49
  • View blog
  • Posts: 2,489
  • Joined: 09-January 09

Re: syntax error in update statement

Posted 10 May 2009 - 06:17 AM

Yup. It's a syntax error.

Here's the correction.

strsql = "Update weeklyAR set Analyst = '" & tempnam & "',Baanco = '" & tempnam1 & "',Customer = '" & Trim(xl.ActiveSheet.Range("C" & i)) & "',Total AR = '" & tempnam2 & "',Current = '" & Trim(xl.ActiveSheet.Range("E" & i)) & "',1-5 Days = '" & Trim(xl.ActiveSheet.Range("F" & i)) & "',6-10 Days = '" & Trim(xl.ActiveSheet.Range("G" & i)) & "',11-30 Days = '" & Trim(xl.ActiveSheet.Range("H" & i)) & "',31-60 Days = '" & Trim(xl.ActiveSheet.Range("i" & i)) & "',61+ Days = '" & Trim(xl.ActiveSheet.Range("j" & i)) & "' where id = '" & Trim(xl.ActiveSheet.Range("D" & i)) & "'"


Was This Post Helpful? 0
  • +
  • -

#4 Prasadh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 03-May 09

Re: syntax error in update statement

Posted 10 May 2009 - 09:33 PM

Thanks nikhil,

I tried but iam getting the same error have attached the vb form , Pls note Insert query is running correctly but , I don't know what is wrong in update query

Below is the code

Sub upld()


xl.Workbooks.Open Trim(Text1.Text), False
excelname = ActiveWorkbook.Name
'Visible = True

xl.ActiveSheet.Range("B65536").Select
Selection.End(xlUp).Select
edrow = ActiveCell.Row
dupcount = 0
upcount = 0
For i = 2 To edrow
Dbcon
'Set cn = CurrentDb.Connection
strsql = "Select * from weeklyAR where Customer = '" & Trim(xl.ActiveSheet.Range("C" & i)) & "'"
tempnam = Replace(Trim(xl.ActiveSheet.Range("A" & i)), "'", "~", , , vbTextCompare)
tempnam1 = Replace(Trim(xl.ActiveSheet.Range("B" & i)), "'", "~", , , vbTextCompare)
tempnam2 = Replace(Trim(xl.ActiveSheet.Range("D" & i)), "'", "~", , , vbTextCompare)
If rsview.State = 1 Then rsview.Close
rsview.Open strsql, cn
If rsview.EOF = True Then
strsql = ("Insert into weeklyAR Values('" & tempnam & "','" & tempnam1 & "','" & Trim(ActiveSheet.Range("C" & i)) & "','" & tempnam2 & "','" & Trim(ActiveSheet.Range("E" & i)) & "','" & Trim(ActiveSheet.Range("F" & i)) & "','" & Trim(ActiveSheet.Range("G" & i)) & "','" & Trim(ActiveSheet.Range("H" & i)) & "','" & Trim(ActiveSheet.Range("I" & i)) & "','" & Trim(ActiveSheet.Range("J" & i)) & "')")
cn.Execute strsql
upcount = upcount + 1
Else
strsql = "Update weeklyAR set Analyst = '" & tempnam & "',Baanco = '" & tempnam1 & "',Customer = '" & Trim(xl.ActiveSheet.Range("C" & i)) & "',Total AR = '" & tempnam2 & "',Current = '" & Trim(xl.ActiveSheet.Range("E" & i)) & "',1-5 Days = '" & Trim(xl.ActiveSheet.Range("F" & i)) & "',6-10 Days = '" & Trim(xl.ActiveSheet.Range("G" & i)) & "',11-30 Days = '" & Trim(xl.ActiveSheet.Range("H" & i)) & "',31-60 Days = '" & Trim(xl.ActiveSheet.Range("i" & i)) & "',61+ Days = '" & Trim(xl.ActiveSheet.Range("j" & i)) & "' where id = '" & Trim(xl.ActiveSheet.Range("D" & i)) & "'"
cn.Execute strsql
dupcount = dupcount + 1

End If

Next i
MsgBox upcount & " Records Uploaded!!!" & vbCrLf & dupcount & " Records Updated!!!, vbInformation"
'vrtSelectedItem.Close
cn.Close
End Sub

Pls note all the field names are correct.

Pls anyone assit

rgds
Prasadh

kindly help
Was This Post Helpful? 0
  • +
  • -

#5 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: syntax error in update statement

Posted 10 May 2009 - 10:26 PM

SQL string looks good. Except can't remember if spaces and punctuation in fieldname can cause issues. I avoid them and don't think I have any fieldnames with spaces and no punctuation except underscore. Suggest building the SQL string in steps, run it and see which one bugs. Ex:

strSQL = strSQL & "Update weeklyAR set Analyst =" & tempnam & "', "
strSQL = strSQL & "Baanco = '" & tempnam1 & "', "
...
Was This Post Helpful? 0
  • +
  • -

#6 firebolt  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 92
  • View blog
  • Posts: 5,561
  • Joined: 20-February 09

Re: syntax error in update statement

Posted 11 May 2009 - 12:41 AM

come on guys. please post in the code tags :code:
Was This Post Helpful? 0
  • +
  • -

#7 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: syntax error in update statement

Posted 11 May 2009 - 06:36 PM

did you notice the space in the update statement try to change like this
[Total AR] = '" & tempnam2
 & "',Current = '" & Trim(xl.ActiveSheet.Range("E" & i))
 & "',[1-5 Days] = '" & Trim(xl.ActiveSheet.Range("F" & i)) 
 & "',[6-10 Days] = '" & Trim(xl.ActiveSheet.Range("G" & i)) & "'


and so on

hope this help you
Was This Post Helpful? 0
  • +
  • -

#8 Prasadh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 03-May 09

Re: syntax error in update statement

Posted 13 May 2009 - 10:10 PM

Thanks it was working but now i got a new syntax error in the same code

as advised have updated the column fields with [ ] now i am getting error as

Run time error "extra( in query expression customer = L53095 "

clarifying myself as update following fields analyst, baanco, etc.,,,,,,, where customer = " "

below is the udpated code


Else
strsql = ("Update weeklyAR set [Analyst] = '" & tempnam & "',[Baanco] = '" & tempnam1 & "',[TotalAR] = '" & tempnam2 & "',[Current] = '" & Trim(xl.ActiveSheet.Range("E" & i)) & "',[1-5Days] = '" & Trim(xl.ActiveSheet.Range("F" & i)) & "',[6-10Days]= '" & Trim(xl.ActiveSheet.Range("G" & i)) & "',[11-30Days] = '" & Trim(xl.ActiveSheet.Range("H" & i)) & "',[31-60Days] = '" & Trim(xl.ActiveSheet.Range("i" & i)) & "',[61+Days] = '" & Trim(xl.ActiveSheet.Range("j" & i)) & "' where [Customer] = '" & Trim(xl.ActiveSheet.Range("C" & i)) & "')")
cn.Execute strsql
dupcount = dupcount + 1


Pls assist

rgds
Prasadh
Was This Post Helpful? 0
  • +
  • -

#9 firebolt  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 92
  • View blog
  • Posts: 5,561
  • Joined: 20-February 09

Re: syntax error in update statement

Posted 13 May 2009 - 11:16 PM

:code:
Was This Post Helpful? 0
  • +
  • -

#10 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: syntax error in update statement

Posted 14 May 2009 - 06:39 PM

strsql = ("Update weeklyAR set [Analyst] = '" & tempnam & "',[Baanco] = '" & tempnam1 & "',[TotalAR] = '" & tempnam2 & "',[Current] = '" & Trim(xl.ActiveSheet.Range("E" & i)) & "',[1-5Days] = '" & Trim(xl.ActiveSheet.Range("F" & i)) & "',[6-10Days]= '" & Trim(xl.ActiveSheet.Range("G" & i)) & "',[11-30Days] = '" & Trim(xl.ActiveSheet.Range("H" & i)) & "',[31-60Days] = '" & Trim(xl.ActiveSheet.Range("i" & i)) & "',[61+Days] = '" & Trim(xl.ActiveSheet.Range("j" & i)) & "' where [Customer] = '" & Trim(xl.ActiveSheet.Range("C" & i)) & "'")



where is the space ?
i don't ask to remove the space between the words keep the space in the query
strsql = ("Update weeklyAR set [Analyst] = '" & tempnam & "',[Baanco] = '" & tempnam1 & "',[Total AR] = '" & tempnam2 & "',[Current] = '" & Trim(xl.ActiveSheet.Range("E" & i)) & "',[1-5 Days] = '" & Trim(xl.ActiveSheet.Range("F" & i)) & "',[6-10 Days]= '" & Trim(xl.ActiveSheet.Range("G" & i)) & "',[11-30 Days] = '" & Trim(xl.ActiveSheet.Range("H" & i)) & "',[31-60 Days] = '" & Trim(xl.ActiveSheet.Range("i" & i)) & "',[61+Days] = '" & Trim(xl.ActiveSheet.Range("j" & i)) & "' where [Customer] = '" & Trim(xl.ActiveSheet.Range("C" & i)) & "'")


or if you remove the space in the column headers then you can change it as like you did

i think i help you
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1