String Handling: Quoting already quoted strings
Hello,
I am building a dynamic function that will create the WHERE clause for a SQL statement based on how other queries returned results.
Here are some excerpts from my code:
Case Statement Background - I have a function that builds the str_SWX using concatenated binary results from other queries. ("001" Query 1 = False, Query 2 = False, Query 3 = True) The str_SWX is then evaulated in this CASE statement as follows:
Then I save the str_WHERE variable into a field in the batch table using this code:
My problem (I think) is within the syntax and the string handling of the strings containing strings. When I go to concatenate the the next SELECT statement with the saved str_WHERE statement, it doesn't handle the text variables correctly and errors out.
What are your thoughts?
Best Regards,
Wendee
I am building a dynamic function that will create the WHERE clause for a SQL statement based on how other queries returned results.
Here are some excerpts from my code:
Case Statement Background - I have a function that builds the str_SWX using concatenated binary results from other queries. ("001" Query 1 = False, Query 2 = False, Query 3 = True) The str_SWX is then evaulated in this CASE statement as follows:
'Case statement
Select Case str_SWX
Case "000" 'No Local Matches
lstSplash.AddItem ("No Local Matches for: " & str_SREGION & _
" OR " & str_COUNTRY)
Call CLOSE_BATCH
Call ADD_REM_REQ
Case "001" 'Match on Subregion ONLY
lstSplash.AddItem ("Match for Subregion: " & str_SREGION)
str_WHERE = " WHERE SubREGION = '" & str_SREGION & "' AND COUNTRY IS NULL "
Call UPDATE_BATCH
Case "010" 'Match on Country ONLY
lstSplash.AddItem ("Match for Country: " & str_COUNTRY)
str_WHERE = " WHERE SubREGION IS NULL AND COUNTRY = '" & str_COUNTRY & "' "
Call UPDATE_BATCH
Case "011" 'Match on Subregion OR Country
lstSplash.AddItem ("Matches for Country: " & str_COUNTRY & _
" AND Subregion: " & str_SREGION)
str_WHERE = " WHERE SubREGION = '" & str_SREGION & "' OR COUNTRY = '" & str_COUNTRY & "' "
Call UPDATE_BATCH
Case "100" 'Match on Subregion AND Country
str_WHERE = " WHERE SubREGION = '" & str_SREGION & "' AND COUNTRY = '" & str_COUNTRY & "' "
Call UPDATE_BATCH
Case Else 'Should not hit this ELSE statement
lstSplash.AddItem ("SERIOUS ERROR! ")
End SelectThen I save the str_WHERE variable into a field in the batch table using this code:
str_SQL = "UPDATE TMP_FCST_BATCH " & _
"SET TMP_FCST_BATCH.SWX = '" & str_SWX & "' " & _
", TMP_FCST_BATCH.WHERE = '" & str_where & "' " & _
"', TMP_FCST_BATCH.PASS = TRUE " & _
" WHERE TMP_FCST_BATCH.ID = " & int_ID
DoCmd.RunSQL (str_SQL)My problem (I think) is within the syntax and the string handling of the strings containing strings. When I go to concatenate the the next SELECT statement with the saved str_WHERE statement, it doesn't handle the text variables correctly and errors out.
What are your thoughts?
Best Regards,
Wendee
Can you stop the code and get a print out of str_SQL at the line DoCmd.RunSQL (str_SQL)?
I think you have the syntax wrong for the update statement.
Should follow:
UPDATE tablename
SET field1=value1, field2=value2,...
WHERE whereclause
Looks like you have too many WHEREs
I think you have the syntax wrong for the update statement.
Should follow:
UPDATE tablename
SET field1=value1, field2=value2,...
WHERE whereclause
Looks like you have too many WHEREs
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@rveliz: this is not my week -- you are right. Let me fix this, then address LSMConsulting's comment.
Thank you.
Thank you.
ASKER
@rveliz: Actually, no, there is not too many where's. when I first looked at it, upon your suggestion, I thought you had a valid point. However, it's saving a WHERE statement into the table. So it is saying that set TMP_FCST_BATCH.WHERE to "Where subregion = ..." So there would be 3 "wheres" in the code. Maybe I should just take the "WHERE " out and concatenate that later.
ahh, ok I see.
Try this then:
Leave single quotes in the "where" string variables and replace the single quotes with two double quotes in the UPDATE
Try this then:
str_SQL = "UPDATE TMP_FCST_BATCH " & _
"SET TMP_FCST_BATCH.SWX = """ & str_SWX & """ " & _
", TMP_FCST_BATCH.WHERE = """ & str_where & """" & _
", TMP_FCST_BATCH.PASS = TRUE " & _
" WHERE TMP_FCST_BATCH.ID = " & int_IDLeave single quotes in the "where" string variables and replace the single quotes with two double quotes in the UPDATE
I always use Chr(34) instead of " and ' when constructing strings.
I also never use & _
SomeString = "Something "
SomeString = SomeString & "Some more stuff"
You can then drop a breakpoint on each line and watch it get built.
Or throw a msgbox in between each line and see what you are getting.
Mega-quotes = Evil.
But that's me
And I DEFINITELY would have used a recordset instead of SQL to do the update.
That mess is just way too hard to debug.
But that's why you posted :)
I also never use & _
SomeString = "Something "
SomeString = SomeString & "Some more stuff"
You can then drop a breakpoint on each line and watch it get built.
Or throw a msgbox in between each line and see what you are getting.
Mega-quotes = Evil.
But that's me
And I DEFINITELY would have used a recordset instead of SQL to do the update.
That mess is just way too hard to debug.
But that's why you posted :)
Best thing to do is set a breakpoint at the runsql line and using the immediate window, get the value of the sql_SQL variable at that point. This way you can see what the sql statement actually looks like. You can copy paste the sql statement to a new query just to verify it's correctness if you like.
ASKER
@Nick67: Thank you for your advice.
I agree with the Mega-Quotes!! :) I have to follow the requirements and specifications for coding and documentation. One requirement is that all SQL is formatted like:
SELECT Field1, ...Fieldx
FROM Table
WHERE CLAUSE
Which (as far as I know) requires me to use the "& _" Right?
As far as the recordset...requirements and specifications require me to write to TMP tables. I am not the first or last developer for this project. I don't have too many liberties here. :(
I agree with the Mega-Quotes!! :) I have to follow the requirements and specifications for coding and documentation. One requirement is that all SQL is formatted like:
SELECT Field1, ...Fieldx
FROM Table
WHERE CLAUSE
Which (as far as I know) requires me to use the "& _" Right?
As far as the recordset...requirements and specifications require me to write to TMP tables. I am not the first or last developer for this project. I don't have too many liberties here. :(
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Nick67,
So are you saying that your method listed above using the With/End statement is or is not optimized for thousand's of records?
This is an app that will be bridging a gap between 2 SAP "modules". The SAP version is not slated to be finished until later this year. So, this will work interim. This will be handling about 30,000 records at once.
Wendee
So are you saying that your method listed above using the With/End statement is or is not optimized for thousand's of records?
This is an app that will be bridging a gap between 2 SAP "modules". The SAP version is not slated to be finished until later this year. So, this will work interim. This will be handling about 30,000 records at once.
Wendee
And... :)
What you build as a shim to test that it works right
SomeString = "Select Something "
SomeString = SomeString & "from someplace "
SomeString = SomeString & "where something = something else"
can be transformed to
SomeString = "Select Something " & _
"from someplace " &_
"where something = something else"
AFTER you know it'll work ;)
What you build as a shim to test that it works right
SomeString = "Select Something "
SomeString = SomeString & "from someplace "
SomeString = SomeString & "where something = something else"
can be transformed to
SomeString = "Select Something " & _
"from someplace " &_
"where something = something else"
AFTER you know it'll work ;)
How many updates does it do in one go is the question.
Does it just update a single record at a time, or is there some code I don't see that this is going to update 30000+ records in one fell go?
Looping is evil when you get into very large datasets--but nothing I've seen suggests a loop.
And if the if the looping is external to this, it makes little difference if the routine executes 30000 one-off updates using SQL or recordset code.
There is a difference if the SQL will update 30000 records in one execution, but the recordset has to update them one at a time
Catch my drift?
Does it just update a single record at a time, or is there some code I don't see that this is going to update 30000+ records in one fell go?
Looping is evil when you get into very large datasets--but nothing I've seen suggests a loop.
And if the if the looping is external to this, it makes little difference if the routine executes 30000 one-off updates using SQL or recordset code.
There is a difference if the SQL will update 30000 records in one execution, but the recordset has to update them one at a time
Catch my drift?
ASKER
It appears that my question has a few solutions.
@LSMConsulting:
Your suggestion to do the Replace, worked. That was an answer to my syntax issue.
@Nick67:
I am going to take your suggestion to the With Statement w/ the RS.
@LSMConsulting:
Your suggestion to do the Replace, worked. That was an answer to my syntax issue.
@Nick67:
I am going to take your suggestion to the With Statement w/ the RS.
ASKER