• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

String Handling: Quoting already quoted strings


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 Select

Open in new window

Then I save the str_WHERE variable into a field in the batch table using this code:
        "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)

Open in new window

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,

  • 6
  • 4
  • 3
  • +1
2 Solutions
pwdellsAuthor Commented:
This is the error: String Error
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You need to escape the single quotes in the str_Where variable before storing it:

        "SET TMP_FCST_BATCH.SWX = '" & str_SWX & "' " & _
            ", TMP_FCST_BATCH.WHERE = '" &  Replace(str_where, "'", "''") & "' " & _
            "', TMP_FCST_BATCH.PASS = TRUE " & _
        " WHERE TMP_FCST_BATCH.ID = " & int_ID

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

pwdellsAuthor Commented:
@rveliz: this is not my week -- you are right.  Let me fix this, then address LSMConsulting's comment.

Thank you.
pwdellsAuthor Commented:
@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:

        "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

Open in new window

Leave 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 :)
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.
pwdellsAuthor Commented:
@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

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'd do the update like so, and not in SQL.  This is debuggable
If you are allowed :(  It's one thing to require 10000 record updates to be done in SQL.
This is one record

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select SWX, WHERE ,PASS from TMP_FCST_BATCH where ID = " & int_ID &  ";", dbOpenDynaset, dbSeeChanges)

With rs
    !SWX = Chr(34) & str_SWX & Chr(34)
    !WHERE = Chr(34) & str_where & Chr(34)
    !PASS = True
End With
pwdellsAuthor Commented:

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.

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 ;)
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?
pwdellsAuthor Commented:
It appears that my question has a few solutions.

Your suggestion to do the Replace, worked.   That was an answer to my syntax issue.

I am going to take your suggestion to the With Statement w/ the RS.


Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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