Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL doesn't show string var

Posted on 2011-03-25
7
Medium Priority
?
325 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

963 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