afreer2
asked on
SQL doesn't show string var
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' ))"
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'
ASKER
The problem is the sql string sees "tableName" instead of 123etc
tableName is a variable outside the string
Lee
strSQL = "Delete * FROM " & tableName & " WHERE (((rHid)='rused'))"
Lee
Your query:
I used the alias tN to make it easier.
Lee
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
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
Lee
ASKER
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.
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.
If you want to Null columns, this is the SQL you need:
References:
SQL DELETE http://www.w3schools.com/s ql/sql_del ete.asp
SQL UPDATE http://www.w3schools.com/s ql/sql_upd ate.asp
Let us know if this isn't what you're trying to do.
Lee
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/s
SQL UPDATE http://www.w3schools.com/s
Let us know if this isn't what you're trying to do.
Lee
Again, are you deleting rows from the table, or values from columns?
Is rused a string value? If so, your WHERE clause should read
Lee
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
ASKER
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
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
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
Lee
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
ASKER
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?
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?
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
Lee
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
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
ASKER
delete row
Might take a while to work thru.
Let me see if I can get it to work
Might take a while to work thru.
Let me see if I can get it to work
To delete rows
If rused is a string
Lee
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window