asked on

SQL doesn't show string var

Private Sub chkR_BeforeUpdate(Cancel As Integer)
Dim strSQL, strSQL1, strSQL2, strSQL3 As String
Dim RecordName, Tn As String
rused = Me.rHid 'value of rhid("SA Feb17y11r08p06")
Tn = "table" & Me.RN 'gives "table" & the value of RN(01 in this case)

If chkR Then ' if checkbox is ticked

strSQL = "INSERT INTO table01 ( rHid, hos, Fps1, Fps2, Fps3, Fps4, Fps5, Fps6, Fps7 )" _
''expects line# or label or end of statment here vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
'"SELECT test.rHid, test.hos, test.Fps1, test.Fps2, test.Fps3, test.Fps4, test.Fps5, test.Fps6, test.Fps7 FROM test" _
''^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
'"WHERE (((test.rHid)= "SA Feb17y11r08p06"))"
' ' tried & Chr(39) and & Chr(34)

Tn = "table01"
rused = "SA Feb17y11r08p06"
strSQL1 = "INSERT INTO " & Tn & " ( rHid,Hos, Fps1, Fps2, Fps3, Fps4, Fps5, Fps6, Fps7 )"
strSQL2 = "SELECT test.rHid, test.Hos, test.Fps1, test.Fps2, test.Fps3, test.Fps4, test.Fps5, test.Fps6, test.Fps7 "
strSQL3 = "FROM test WHERE (test.rHid) = & Chr(39) & rused & Chr(39)"
strSQL = strSQL1 & strSQL2 & strSQL3 ' sees Tn as "table01" sees (test.rHid) = & Chr(39) & rused & Chr(39)

CurrentDb.Execute strSQL, dbFailOnError
Else
'this works vvvvvvvvvvvvvvvvvvvvvvvv
strSQL = "DELETE FROM " & Tn & " WHERE rHid = & Chr(39) & rused & Chr(39)"
'CurrentDb.Execute strSQL, dbFailOnError
End If
End Sub
I thought I could work out this when Lee helped with "delete from" sql
I'm missing something

ALaRiva

The correct method for continuing a line is ... & _

You are missing the & symbol.

Also, for literal strings, you need to use single quote.

So change:
strSQL = "INSERT INTO table01 ( rHid, hos, Fps1, Fps2, Fps3, Fps4, Fps5, Fps6, Fps7 )" _
''expects line# or label or end of statment here vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
'"SELECT test.rHid, test.hos, test.Fps1, test.Fps2, test.Fps3, test.Fps4, test.Fps5, test.Fps6, test.Fps7 FROM test" _
''^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
'"WHERE (((test.rHid)= "SA Feb17y11r08p06"))"
' ' tried & Chr(39) and & Chr(34)

To this:
strSQL = "INSERT INTO table01 ( rHid, hos, Fps1, Fps2, Fps3, Fps4, Fps5, Fps6, Fps7 ) " & _
"SELECT test.rHid, test.hos, test.Fps1, test.Fps2, test.Fps3, test.Fps4, test.Fps5, test.Fps6, test.Fps7 FROM test " & _
" WHERE (((test.rHid)= 'SA Feb17y11r08p06'))"

hth

- Anthony

ALaRiva

Oops, missed a whole section. Try this:

strSQL = "INSERT INTO table01 ( rHid, hos, Fps1, Fps2, Fps3, Fps4, Fps5, Fps6, Fps7 ) " & _
"SELECT test.rHid, test.hos, test.Fps1, test.Fps2, test.Fps3, test.Fps4, test.Fps5, test.Fps6, test.Fps7 FROM test " & _
"WHERE (((test.rHid)= '" & rused & "'))"

afreer2

ASKER

Ok that works for the test part but the variable part is what I can't get to work

strSQL = "INSERT INTO " & Tn & " ( rHid,Hos, Fps1, Fps2, Fps3, Fps4, Fps5, Fps6, Fps7 )" & _
"SELECT test.rHid, test.Hos, test.Fps1, test.Fps2, test.Fps3, test.Fps4, test.Fps5, test.Fps6, test.Fps7" & _
"FROM test WHERE (test.rHid) = & Chr(39) & rused & Chr(39)"
' sees Tn as "table01" sees (test.rHid) = & Chr(39) & rused & Chr(39)

ALaRiva

Have a look at my second post. I missed changing the variable part in the first one.

- Anthony

afreer2

ASKER

that works
this doesn't
strSQL = "INSERT INTO table01 ( rHid, hos, Fps1, Fps2, Fps3, Fps4, Fps5, Fps6, Fps7 ) " & _
"SELECT test.rHid, test.hos, test.Fps1, test.Fps2, test.Fps3, test.Fps4, test.Fps5, test.Fps6, test.Fps7 FROM test " & _
"WHERE (((test.rHid)= & Chr(39) & Chr(34) & rused & Chr(34) & Chr(39)))"
do I have the chr# right?

ASKER CERTIFIED SOLUTION

ALaRiva

membership

This solution is only available to members.

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

Start Free Trial

afreer2

ASKER

I knew that table01 had to be changed
I was trying to see why the CHR(39) CHR(34) didn't work I can see why it is suggested to use those
instead of ' and " so those are small and CHR() is big good for old people like me(-:
strSQL = "INSERT INTO " & Tn & "( rHid, hos, Fps1, Fps2, Fps3, Fps4, Fps5, Fps6, Fps7 ) " & _
"SELECT test.rHid, test.hos, test.Fps1, test.Fps2, test.Fps3, test.Fps4, test.Fps5, test.Fps6, test.Fps7 FROM test " & _
"WHERE (((test.rHid)= '" & rused & "'))"