?
Solved

SQL doesn't show string var

Posted on 2011-03-25
7
Medium Priority
?
331 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:afreer2
  • 4
  • 3
7 Comments
 
LVL 10

Expert Comment

by:ALaRiva
ID: 35219706
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
0
 
LVL 10

Expert Comment

by:ALaRiva
ID: 35219742
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 & "'))"
0
 

Author Comment

by:afreer2
ID: 35219781
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)
 
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:ALaRiva
ID: 35219793
Have a look at my second post. I missed changing the variable part in the first one.

- Anthony
0
 

Author Comment

by:afreer2
ID: 35219836
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?
0
 
LVL 10

Accepted Solution

by:
ALaRiva earned 2000 total points
ID: 35219862
You cannot use Chr(34) inside of a literal. You have to use the actual " to terminate the literal before you can use any coded ascii values.

Try this if you really want to go that route.

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) & rused & Chr(39) "))"

IMO, what I provided is easier to read, but each person has their own views.

hth

- Anthony
0
 

Author Comment

by:afreer2
ID: 35219930
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 & "'))"
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question