syntax error in sql statement

[John Segars]

I know there is some error in use of ' and " but can't find how to fix
it.It is probably in where clause where I try to use variable as
criterion in expression.
I get syntax error in Append query when I run this vba code:
---
Sub update_tblschlrptsummary()
On Error GoTo update_tblschlrptsummary_Err
Dim db As Database, rst As Recordset, strSQL As String
Dim subj(3) As String ' subtests
subj(0) = "ELA"
subj(1) = "Math"
Dim rcw(6) As String ' report card weight labels
rcw(1) = "BB1"
rcw(2) = "BB2"
rcw(3) = "Bas"
rcw(4) = "Prf"
rcw(5) = "Adv"
Dim grd(12) As String 'grade level
grd(3) = "3"
grd(4) = "4"
grd(5) = "5"
grd(6) = "6"
grd(7) = "7"
grd(8) = "8"
Set db = CurrentDb()
For g = 3 To 8 'grade
For r = 1 To 5
For s = 0 To 1
strSQL = "UPDATE tblSchlRptCard04_frqDist INNER JOIN tblSchlRptSummary
"
strSQL = strSQL & "ON tblSchlRptCard04_frqDist.schlname =
tblSchlRptSummary.SchoolName "
strSQL = strSQL & "Set tblSchlRptSummary.'& subj(s)&'_'& rcw(r) & '_'&
grd(g) & ' = [' & subj(s) & '_#]'"
REM: set shld look like this: tblSchlRptSummary.ELA_BB1_3 = [ELA_#]
strSQL = strSQL & "WHERE ((tblSchlRptCard04_frqDist.Grade)=" &
''grd(g)''&")"
strSQL = strSQL & " and ((tblSchlRptCard04_frqDist.RCW)='rcw(r)')"
REM: where shld be(tblSchlRptCard04_frqDist.Grade)='04')and
((tblSchlRptCard04_frqDist.RCW)='1'

DoCmd.RunSQL (strSQL)
Next s
Next r
Next g
---

 

[Randy Harris]

John, single quotes embedded within double quotes are interpreted literally.
That is:

strSQL = "SELECT table.' & sub(1) & '..."

well be just exactly what is between the double quotes.

If you use:

strSQL = "SELECT table." & sub(1) & "..."

then the value will be substituted for sub(1), rather than the literal
characters s, then u, then b, then (, etc

It could be very helpful to temporarily comment out the line:
DoCmd.RunSql (strSQL)
and insert a line:
Debug.Print strSQL

Then you will see, in the Immediate window, the SQL string that is created
for Jet. That will make it MUCH easier to debug.

Randy