String search for a literal Double Quote

[gblack]

I have an SQL (string) variable named: strSQL.

I am trying to use an inputbox (or even a textbox) to manually alter the SQL code and change the QueryDef.

So (let's just say for the "Where Clause") I have something like:
strSQL = inputBox("Enter Where Clause")

The issue is, the InputBox double-quotes get doubled up.

For Example:
If I enter (into my InputBox):
Where [Table1].[Field1] Like "*000*"

Access changes my strSQL variable to post with an extra set of double quotes:
Where [Table1].[Field1] Like ""*000*""
---------------------------------------------------------------------------------------

If I enter (into my InputBox) no quotes:
Where [Table1].[Field1] Like *000*

my strSQL variable will post without quotes:
Where [Table1].[Field1] Like *000*
---------------------------------------------------------------------------------------

If I enter single quotes (into my InputBox):
Where [Table1].[Field1] Like '*000*'

Access changes the single quotes to double quotes, and adds an extra set of double quotes:
Where [Table1].[Field1] Like ""*000*""
---------------------------------------------------------------------------------------

I've tried using the Replace() function some examples:
Replace(strSQL, """", """)
Replace(strSQL, """", "& chr(34) &")
Replace(strSQL, "" & chr(34) & chr(34) & "", "& chr(34) &")
none of these work!
---------------------------------------------------------------------------------------

So I want to run through strSQL (after the fact) and create a function that parses through the entire string to remove the double-double quotes (i.e. "") and change them to one double quote (i.e. ")

But I don't know how to search for a literal double quote (") within a string, let alone two ("") side by side within the string...

for example, these won't work:
Instr(strSQL, """)
Instr(strSQL, "" & chr(34) & "")
Instr(strSQL, " & chr(34) & ")



Very Respectfully,
Gary

 

[Ranman256]

use queries instead of SQL and you wont need to bother with the quotes.
the query would read off a form.

 

[gblack]

Thank you,

I appreciate the suggestion, but I really want to know how to deal with this (above) issue.

There's a lot more to what I am doing than just what I have stated. I really don't want to entirely revamp my project, because I can't remove a quote.

It seems to be that there should be a way: to search for a literal within the string, then remove it. I'm sure someone in this forum knows how... Or can state, definitively, that it's impossible.

Respectfully,
-G

 

[MajP]

Can you explain this better? Because I cannot replicated the behavior you show. I understand that you are typing that into an input box. I do not get any changes to single or double quotes.

 

[theDBguy]

Hi Gary. At first look, I couldn't see any problems with you getting double quotes from the input box. But when I gave it a try, I also don't get the double quotes as MajP. Are you doing other things to the result of the input box? If so, can you please show us your complete code? Thanks.

 

[isladogs]

Suggest you use a combo based on a table or query with all allowed values 000, 001 etc.
Then build your where clause based on that value and you will circumvent the problem as well as make life easier for the end user.

 

[gblack]

I've attached what I am doing...

I already have a where clause started off (see first screenshot) in the Variable "Where [J04A].[ITEMNUM]

in the InputBox I post:
Like "*000*"

then click ok...

A message box posts the value of the strSQL and it states that my where clause looks fine (see second screenshot):

i.e. Where [J04A].[ITEMNUM] Like "*000*"

Which is exactly what I want... but...

When I actually post the output of the strSQL (third screen shot), the where clause somehow gets additional double quotes added:

Where [J04A].[ITEMNUM] Like ""*000*""

So I can't push that SQL into a QueryDef, because:

Select * From [J04A] Where [J04A].[ITEMNUM] Like ""*000*""

is not a valid SQL statement.

R,
-G

 

[theDBguy]

Hi. Can you show us what your code is doing between screenshot 2 and 3? How exactly are you putting your variable WHERE clause value together with the input from the user?

 

[isladogs]

I haven't tested this myself but did you read my suggestion for an alternative (better?) way of doing this which WILL work and be easier for users

 

[MajP]

I agree there are much nicer user interface, but still do not get that

Public Sub TestIt()
Debug.Print InputBox("Enter Search")
End Sub

Like '*000*'

 

[gblack]

DB Guy... Here's my code (it's not exactly as simple as I posted prior, as I was just trying to get the point across). It uses a Double-Click event from a List Box to grab the Table and Field for the Where clause...

Private Sub lbxBodySQL_DblClick(Cancel As Integer)
Dim strCriteriaA As String
Dim strCriteriaB As String
Dim strSelection As String
Dim intAnswer As Integer
Dim strInputBox As String

'Gets Table and Column
strSelection = "[" & Me!lbxBodySQL.Column(1) & "].[" & Me!lbxBodySQL.Column(2) & "]"

gstr_WhereSQL = Nz(gstr_WhereSQL, "")


If gstr_WhereSQL = "" Then
    strEMPTY = "EMPTY"
End If
'Creates your where clause

intAnswer = vbNo
'Prompt for Where Clause
Do While 1 = 1 'Infinite loop (on purpose in order to allow the Where Clause to be changed over and over)
    
    Select Case intAnswer
        Case vbYes
            gstr_WhereSQL = strCriteriaB
            'MsgBox ("Your WHERE CLAUSE looks like: " & vbNewLine & gstr_WhereSQL)
            Exit Sub
        Case vbNo
            'InputBox will add criteria
            If gstr_WhereSQL = "" Or gstr_WhereSQL = " WHERE " Then
                strCriteriaA = " WHERE "
                gstr_WhereSQL = " WHERE "
            Else
                
                'vbNullString
                
                strInputBox = InputBox("The Current WHERE CLAUSE Looks like this: " & vbNewLine & _
                                                        "|" & gstr_WhereSQL & "|" & vbNewLine & _
                                                        "Choose: AND/OR/Parens...etc...")
                                                        
                If strInputBox = vbNullString Then
                    Exit Sub
                Else
                    strCriteriaA = gstr_WhereSQL & " " & strInputBox & " "
                End If
            
            End If
                'Add to the current WHERE CLAUSE.
                
                strInputBox = InputBox(strCriteriaA & strSelection & vbNewLine & _
                                                "[Add your criteria here...]")
                                                
                If strInputBox = vbNullString Then
                    Exit Sub
                Else
                    strCriteriaB = strCriteriaA & strSelection & " " & strInputBox
                End If
                                                           
                    strCriteriaB = Replace(strCriteriaB, "'", Chr(34))
                'Are you satisfied with your WHERE CLAUSE?
                intAnswer = MsgBox("Your WHERE CLAUSE will look like: " & vbNewLine & vbNewLine & _
                                    strCriteriaB & vbNewLine & vbNewLine & _
                    " Click: " & vbNewLine & _
                    " YES....... To Apply the Where Clause " & vbNewLine & _
                    " NO....... To Edit the Where Clause " & vbNewLine & _
                    " CANCEL To Reset the Where Clause ", vbYesNoCancel)
        Case vbCancel
            gstr_WhereSQL = " WHERE "
            MsgBox ("WHERE CLAUSE has been reset")
        
            Exit Sub
    End Select
Loop

End Sub

 

[gblack]

Sorry between 2 and 3 I am simply posting the Variable to txt file:

Private Sub btnFullSQL_Click()
Dim strDesktop As String
        
    strDesktop = Environ("USERPROFILE") & "\Desktop"

    Open strDesktop & "\SQL_STRING.txt" For Output As #1 'Append As #1 '
    Write #1, gstr_WhereSQL
    Close #1
End Sub

 

[theDBguy]

Sorry between 2 and 3 I am simply posting the Variable to txt file:

Private Sub btnFullSQL_Click()
Dim strDesktop As String
        
    strDesktop = Environ("USERPROFILE") & "\Desktop"

    Open strDesktop & "\SQL_STRING.txt" For Output As #1 'Append As #1 '
    Write #1, gstr_WhereSQL
    Close #1
End Sub

Thanks. Instead of Write, try using Print to see if it makes a difference.

 

[MajP]

You say that Access is replacing the single with double, where does that take place. Your logic is what is replacing the single with double quotes.
Your forcing it to replace single with double quotes.

strCriteriaB = Replace(strCriteriaB, "'", Chr(34))

 

[gblack]

Your logic is what is replacing the single with double quotes.
Your forcing it to replace single with double quotes.

Yes, I have been fiddling around with the code, trying to make it work... so that part is my fault...However and even so... it still adds extra double quotes. ""*000*""

 

[gblack]

Putting all else aside...and what's making this value post double-double-quotes...

My main question is:
Is there a way to search for a double-quote, within a string variable?

 

[theDBguy]

Putting all else aside...and what's making this value post double-double-quotes...

My main question is:
Is there a way to search for a double-quote, within a string variable?

Hi. Just curious... Did you see my reply above when you answered my question about what your code is doing between images 2 and 3? I was wondering if it worked or not. Can you please let us know? Thanks.

 

[MajP]

Public Sub RemoveDouble()
  Dim str As String
  str = " like """"000"""""
  Debug.Print str
  Debug.Print Replace(str, Chr(34) & Chr(34), "'")
End Sub

like ""000""
 like '000'

 

[MajP]

The reason it doubles up is that your replace code runs twice.

strCriteriaB = Replace(strCriteriaB, "'", Chr(34))

First time you come in vbno and it falls through the select case. Then it does it again depending on when you save the string.