[Prasadh]

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

[firebolt]

please post your code in the code tags. Like this Thanks

[Nikhil_07n]

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)) & "'"

[Prasadh]

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

[June7]

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 & "', "
...

[firebolt]

come on guys. please post in the code tags

[thava]

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

[Prasadh]

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

[firebolt]

[thava]

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