Solved

SQL doesn't show string var

Posted on 2011-03-24
16
242 Views
Last Modified: 2012-05-11
strSQL shows "tableName" and not the assigned var
I put 'tablename' still no good
Dim strSQL, strSQL1, strSQL2, strSQL3 As String
    Dim RecordName, tableName As String
    rused = Me.rHid
    tableName = "table" & Me.RN
    strSQL = "Delete tableName.rHid, tableName.Fps1, tableName.Fps2, tableName.Fps3, tableName.Fps4, tableName.Fps5, tableName.Fps6, tableName.Fps7 FROM tableName WHERE (((tableName.rHid)='rused'))"
0
Comment
Question by:afreer2
  • 9
  • 6
16 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35212433
Is it Access db? try DELETE *
strSQL shows "tableName" and not the assigned var
I put 'tablename' still no good
Dim strSQL, strSQL1, strSQL2, strSQL3 As String
    Dim RecordName, tableName As String
    rused = Me.rHid
    tableName = "table" & Me.RN
    strSQL = "Delete * FROM tableName WHERE (((rHid)='rused'))" 

Open in new window

0
 

Author Comment

by:afreer2
ID: 35212448
The problem is the sql string sees "tableName" instead of 123etc
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 35212470
tableName is a variable outside the string

strSQL = "Delete * FROM " & tableName & " WHERE (((rHid)='rused'))"

Lee
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 35212480
Your query:

strSQL = "DELETE tN.rHid, tN.Fps1, tN.Fps2, tN.Fps3, tN.Fps4, tN.Fps5, tN.Fps6, tN.Fps7 FROM " & tableName & " AS tN WHERE (((tN.rHid)='rused'))"

I used the alias tN to make it easier.

Lee
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 35212489
Wait, what are you "deleting"? SQL DELETE deletes rows, not column values. Are you trying to set columns to Null? What exactly are you trying to do?

Lee
0
 

Author Comment

by:afreer2
ID: 35212519
ok I see the correct tables
 strSQL = "Delete " & tableName & ".rHid, tableName.Fps1, tableName.Fps2, tableName.Fps3, tableName.Fps4, tableName.Fps5, tableName.Fps6, tableName.Fps7 FROM tableName WHERE (((tableName.rHid)=" & rused & "))"
but It's looking for end of statement.
I need a , or ) some where I think.
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 35212521
If you want to Null columns, this is the SQL you need:

strSQL = "UPDATE " & tableName & " AS tN SET tN.rHid = Null, tN.Fps1 = Null, tN.Fps2 = Null, tN.Fps3 = Null, tN.Fps4 = Null, tN.Fps5 = Null, tN.Fps6 = Null, tN.Fps7 = Null WHERE tN.rHid='rused'"
Again, I used the table alias tN. I also deleted all those "()" in the WHERE clause. I don't know why Access uses so many, but it makes it very difficult to read. Technically, you don't need the table anywhere but the FROM clause because it's the only table in play here. This would also make it simpler and more readable.

References:
SQL DELETE  http://www.w3schools.com/sql/sql_delete.asp
SQL UPDATE  http://www.w3schools.com/sql/sql_update.asp

Let us know if this isn't what you're trying to do.

Lee
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 35212536
Again, are you deleting rows from the table, or values from columns?

Is rused a string value? If so, your WHERE clause should read

WHERE (((tN.rHid) = " & Chr(39) & rused & Chr(39) & "))"
If rused is a string, you still need to enclose it in single quotes.

Lee
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:afreer2
ID: 35212617
   strSQL1 = "Delete " & Tn & ".rHid, " & Tn & ".Fps1, " & Tn & ".Fps2, " & Tn & ".Fps3, " & Tn & ".Fps4, " & Tn & ".Fps5, " & Tn & ".Fps6, " & Tn & ".Fps7 FROM " & Tn & " WHERE (((" & Tn & ".rHid)=" & rused & "))"
strSQL2 = "UPDATE " & " & Tn & " & " AS tN SET tN.rHid = Null, tN.Fps1 = Null, tN.Fps2 = Null, tN.Fps3 = Null, tN.Fps4 = Null, tN.Fps5 = Null, tN.Fps6 = Null, tN.Fps7 = Null WHERE tN.rHid=" & rused & ""
    strSQL = strSQL1 & strSQL2
looking for end of statment
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 35212651
No, what system are you working in? Access? Please see the link I provided above for the SQL DELETE statement. It does not work with fields as you have shown in strSQL1.

The WHERE clause in strSQL2 should read

WHERE tN.rHid = " & Chr(39) & rused & Chr(39)
If this is Access, you cannot combine SQL statements as you are in

strSQL = strSQL1 & strSQL2
You must run them separately.

Lee
0
 

Author Comment

by:afreer2
ID: 35212699
Access
Showing the novice I am
I do this for my own pleasure.
I learn as I go.
does the
 CurrentDb.Execute strSQL, dbFailOnError
sit the two sql's?
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 35212742
You still haven't told me exactly what you're trying to do with the DELETE statement. Are you trying to delete rows from the table, or delete values from columns in rows matching the WHERE clause? If the former, use DELETE. If the latter, use UPDATE.

Your code should follow this outline

Dim db As DAO.Database, strSQL As String
Set db = CurrentDb

Other variables

strSQL = first SQL statement

db.Execute strSQL, dbFailOnError

strSQL = second SQL statement

db.Execute strSQL, dbFailOnError

Set db = Nothing

Lee
0
 

Author Comment

by:afreer2
ID: 35212762
delete row
Might take a while to work thru.
Let me see if I can get it to work
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 35212784
To delete rows

If rused is a string
strSQL = "DELETE FROM " & tableName & " WHERE rHid = " & Chr(39) & rused & Chr(39)
If rused is a number
strSQL = "DELETE FROM " & tableName & " WHERE rHid = " & rused

Lee
0
 

Author Comment

by:afreer2
ID: 35212838
string
had to look it up Chr(39) is a single quotation mark and Chr(34) is a double quotation mark.
When I read the strSQL I sse the right var. what does Chr(39) change?
Anyway it works
0
 
LVL 13

Accepted Solution

by:
lee555J5 earned 500 total points
ID: 35214706
Chr(39) inserts a single quote, and the pair wraps the string value in rused in single quotes which are necessary for string comparisons as in

WHERE rHid = " & Chr(39) & rused & Chr(39)
It's easier to see and understand than

WHERE rHid = '" & rused & "'"
It's very helpful when you are dealing with several layers of " and '.

Lee
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now