Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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
0
afreer2
Asked:
afreer2
  • 4
  • 3
1 Solution
 
ALaRivaCommented:
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
 
ALaRivaCommented:
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
 
afreer2Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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

- Anthony
0
 
afreer2Author Commented:
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
 
ALaRivaCommented:
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
 
afreer2Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now