Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL doesn't show string var

Posted on 2011-03-25
7
297 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

856 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