Syntax for sql statement help

access 2003 vba

I can't figure out the syntax for this statement...what is wrong ?

any help ?

Thanks
fordraiders
With rsSql
sqlCmd = "UPDATE (tblNounMod1 INNER JOIN tblTable ON tblNounMod1.ITEM = tblTable.ITEM) INNER JOIN RANKLST2 ON tblTable.ITEM = RANKLST2.ITEMNUM "
sqlCmd = sqlCmd & "SET tblTable.fldScore = score , "
sqlCmd = sqlCmd & "tblTable.fldFndNouns = ""(found: "" & wdFound & "" )"" , "
sqlCmd = sqlCmd & "tblTable.fldDid = rsCust.Fields(""fldDid"").Value , "
sqlCmd = sqlCmd & "tblTable.fldMfgname = rsCust.Fields(""fldMfgname"").Value , "
sqlCmd = sqlCmd & "tblTable.fldMfgnameOrig = rsCust.Fields(""fldMfgnameOrig"").Value , "
sqlCmd = sqlCmd & "tblTable.fldMfrnumST = rsCust.Fields(""fldMfrnum"").Value , "
sqlCmd = sqlCmd & "tblTable.fldMfrnumOrigST = rsCust.Fields(""fldMfrnumOrig"").Value , "
sqlCmd = sqlCmd & "tblTable.fldDescription = Trim(rsCust.Fields(""fldDescription"").Value) , "
sqlCmd = sqlCmd & "tblTable.fldDescriptionOrig = rsCust.Fields(""fldDescriptionOrig"").Value "
sqlCmd = sqlCmd & "WHERE (((tblTable.SPIN) Not Like ""TP"" AND ((tblNounMod1.Fasteners)=(""Y""))))"
End With

Open in new window

LVL 3
FordraidersAsked:
Who is Participating?
 
TextReportConnect With a Mentor Commented:
OK now I have had a little time please find below how I would lay it out following these rules.

1. Use the Line Continuation Character of _ so you don't have to keep repeating sqlCmd = sqlCmd & 
2. break the lines up into logical entities and put a vbcrlf at the end of the line, this puts in a CRLF and helps you to turn your SQL from a single line to a block of code, this is easier to read and spot errors
3. Alias you table names, this reduces the length of your SQL statement and makes it easier to swap one table for another. This is done by TABLENAME X in the FROM clause
4. When doing string concatenation like this if you need a ' or " in the string use Chr(39) or Chr(34) rather than two ' or two " as it is much easier to read

Finally it appears that RANKLST2 isn't needed though this may be the table that holds score

Please note that I have assumed all fields are text any that are numbers then remove the  & Chr(39) before and after the rsCust reference

Cheers, Andrew
sqlCmd = "UPDATE (tblNounMod1 N" & vbCrLf & _
         "         INNER JOIN tblTable T ON N.ITEM = T.ITEM)" & vbCrLf & _
         "         INNER JOIN RANKLST2 R ON T.ITEM = R.ITEMNUM" & vbCrLf & _
         "SET T.fldScore = score" & vbCrLf & _
         "  , T.fldFndNouns = " & Chr(39) & "(found: " & wdFound & " )" & Chr(39) & vbCrLf & _
         "  , T.fldDid = " & Chr(39) & rsCust!fldDid & Chr(39) & vbCrLf & _
         "  , T.fldMfgname = " & Chr(39) & rsCust!fldMfgname & Chr(39) & vbCrLf & _
         "  , T.fldMfgnameOrig = " & Chr(39) & rsCust!fldMfgnameOrig & Chr(39) & vbCrLf & _
         "  , T.fldMfrnumST = " & Chr(39) & rsCust!fldMfrnum & Chr(39) & vbCrLf & _
         "  , T.fldMfrnumOrigST = " & Chr(39) & rsCust!fldMfrnumOrig & Chr(39) & vbCrLf & _
         "  , T.fldDescription = " & Chr(39) & Trim(rsCust!fldDescription) & Chr(39) & vbCrLf & _
         "  , T.fldDescriptionOrig = " & Chr(39) & rsCust!fldDescriptionOrig & Chr(39) & vbCrLf & _
         "WHERE T.SPIN <> " & Chr(39) & "TP" & Chr(39) & vbCrLf & _
         "  AND N.Fasteners=" & Chr(39) & "Y" & Chr(39)

Open in new window

0
 
BadotzCommented:
What does Access say is wrong with it?
0
 
TextReportCommented:
The basic problem appears to be that you are putting your vba reference into the string that is you sql. What I mean by this is that you have

strSQL = "SELECT * FROM table WHERE Field=rsCust.Fields(""fldMfrnum"").Value"

Where as what you need is

strSQL = "SELECT * FROM table WHERE Field='Value'" which is achieved for number by
strSQL = "SELECT * FROM table WHERE Field=" & rsCust.Fields(""fldMfrnum"").Value

For Text it is
strSQL = "SELECT * FROM table WHERE Field=" & Chr(39) & rsCust.Fields(""fldMfrnum"").Value & Chr(39)

For Dates it is
strSQL = "SELECT * FROM table WHERE Field=#" & Format(rsCust.Fields(""fldMfrnum"").Value,"MM/DD/YYYY") & "#"

Cheers, Andrew

0
 
James ElliottConnect With a Mentor Managing DirectorCommented:
Difficult to see what are meant to be variables, and what are meant to be passed values, but this might work for you:

With rsSql
    sqlCmd = "UPDATE (tblNounMod1 INNER JOIN tblTable ON tblNounMod1.ITEM = tblTable.ITEM) INNER JOIN RANKLST2 ON tblTable.ITEM = RANKLST2.ITEMNUM "
    sqlCmd = sqlCmd & "SET tblTable.fldScore = 'score' ,"
    sqlCmd = sqlCmd & "tblTable.fldFndNouns = 'found:' " & wdFound & ","
    sqlCmd = sqlCmd & "tblTable.fldDid =" & rsCust.Fields("fldDid").Value & ","
    sqlCmd = sqlCmd & "tblTable.fldMfgname =" & rsCust.Fields("fldMfgname").Value & ","
    sqlCmd = sqlCmd & "tblTable.fldMfgnameOrig = " & rsCust.Fields("fldMfgnameOrig").Value & ","
    sqlCmd = sqlCmd & "tblTable.fldMfrnumST = " & rsCust.Fields("fldMfrnum").Value & ","
    sqlCmd = sqlCmd & "tblTable.fldMfrnumOrigST = " & rsCust.Fields("fldMfrnumOrig").Value & ","
    sqlCmd = sqlCmd & "tblTable.fldDescription = " & Trim(rsCust.Fields("fldDescription").Value) & ","
    sqlCmd = sqlCmd & "tblTable.fldDescriptionOrig = " & rsCust.Fields("fldDescriptionOrig").Value
    sqlCmd = sqlCmd & "WHERE (((tblTable.SPIN) Not Like 'TP' AND ((tblNounMod1.Fasteners)=('Y'))))"
End With
0
 
BadotzCommented:
<$.02>
If you go the Chr(39) and/or Chr(34) route, plase make them variables:

Dim cA As String
Dim cQ As String

cA = Chr(39) ' apostrophe
cQ = Chr(34) ' double-quote
</$.02>
0
All Courses

From novice to tech pro — start learning today.