asked on

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

Badotz

What does Access say is wrong with it?

TextReport

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

SOLUTION

James Elliott

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION