Can I escape both ' and " ? (1 Viewer)

MarkK · Mar 22, 2018

Mark_, I guess that could be. Kirkm has not provided sample data for what dat(0) might hold. I assumed it was a string with embedded delimiters, like...

Code:

8' x 2"x4"

...which is impossible to delimit without altering the data, or using a parameterized QueryDef. If it is what you are saying, that he wants to insert completed strings as parts of the WHERE clause itself, then what I am talking about will not work.
The problem is there's not enough info from kirkm to understand his problem, so we're just guessing.
Mark

kirkm · Mar 22, 2018

Thanks for the responses

. Markk, I can't follow your function (much as I'd like to). You're passing in LC. LC is the name of a field in the table. Dat(0) is the string datatype (yes. Mark) and Where criteria. I also can't see what your p0 is (I asked you about that before). Here's what I'm using now which is not robust and a bit of a hack just to get past this point. If a parameterized QueryDef is a better solution I'd like to learn how to use it.

Code:

        'get the Records for LC
            If InStr(Dat(0), "'") > 0 Then
                SQL = "Select * from tbl2000 where LC = " & Chr$(34) & Dat(0) & Chr$(34) & " Order by tbl2000.date;"
                Else
                SQL = "Select * from tbl2000 where LC = '" & Dat(0) & "' Order by tbl2000.date;"
            End If
            Set Entries = CurrentDb.OpenRecordset(SQL)

Maybe that helps show something? I'm not inserting or delimiting anything. Apart from Dates, all variables are strings. Pse let me know what /any other info is needed. LC is text that I want to match against Dat(0), also text. Any text will do. Containing some ' or " characters.

Mark_ · Mar 22, 2018

Can you post some sample values for Dat(0) so we can see exactly how you are currently using it?

Also, if you Debug.Print SQL prior to your set, that can help us see what is being generated and fed to your database.

MarkK · Mar 22, 2018

Now we are getting somewhere. So copy the function I wrote into your module, and then change this code...

Code:

        'get the Records for LC
            If InStr(Dat(0), "'") > 0 Then
                SQL = "Select * from tbl2000 where LC = " & Chr$(34) & Dat(0) & Chr$(34) & " Order by tbl2000.date;"
                Else
                SQL = "Select * from tbl2000 where LC = '" & Dat(0) & "' Order by tbl2000.date;"
            End If
            Set Entries = CurrentDb.OpenRecordset(SQL)

...to this...

Code:

        'get the Records for LC
            Set Entries = GetEntriesRecordset(dat(0))

...and the GetRecordset function should look like...

Code:

Private Function GetEntriesRecordset(LC As String) As DAO.Recordset
    Const SQL As String = _
        "SELECT t.* " & _
        "FROM tbl2000 As t " & _
        "WHERE t.LC = [COLOR="Blue"]p0[/COLOR] " & _
        "ORDER BY t.Date"
    
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("[COLOR="blue"]p0[/COLOR]") = LC
        Set GetEntriesRecordset = .OpenRecordset
        .Close
    End With
End Sub

p0 is the name of the parameter we are using in the SQL.
Lemme know if that works, and/or what snags/errors you run into...
Mark

Mark_ · Mar 22, 2018

With CurrentDb.CreateQueryDef("", SQL)
.Parameters("p0") = LC
Set GetEntriesRecordset = .OpenRecordset
.Close
End With

Should be Dat(0)

MarkK · Mar 22, 2018

Mark_, no, you see that is a function, right, with a parameter called LC?
Mark

Mark_ · Mar 23, 2018

@ MarkK... Gotcha... He's using LC as a field name and your using it as a variable.

Normally I make very clear what variables I am using in a function. I'd have called it psParam or something to make it very clear I'm NOT using the same variable.

kirkm · Mar 23, 2018

Markk first thanks for persevering with this. There's a heap of valuable new concepts. I think I get it now, and its working perfectly !
The "As t" got me where t was never defined or setup.Then CreateQueryDef("", sql). I see now the "" is Name meaning an existing query (and no sql part needed?)
It's interesting... so this is a parametized query def? It became clearer when you said to replace mine with this.
So you could have more parameters and call them anything, e.g pO, as long as it's included in the sql? And never run into a problem with quotes?

Mark, FYI my problem first showed with these values

? dat(0)
Black & Blue / Kickin'NEOCD053
? sql
Select * from tbl2000 where LC = 'Black & Blue / Kickin'NEOCD053' Order by tbl2000.date;

So I think I'm cooking with gas finally.. thanks again. This is great.

MarkK · Mar 23, 2018

kirkm said:
So you could have more parameters and call them anything, e.g pO, as long as it's included in the sql? And never run into a problem with quotes?

Yes, and not just quotes, but also date delimiters, they're all handled automatically by the QueryDef object. The code to do it this way is sometimes a few lines longer, but in my opinion it is much more readable, like the difference between...

Code:

"WHERE Field1 = '" & Me.txtFld1 & "' AND Field2 = #" & Me.txtDate2 & "#"

...and...

Code:

"WHERE Field1 = p0 AND Field2 = p1"
...
   .parameters("p0") = Me.txtFld1
   .parameters("p1") = Me.txtDate2
...

...so the second one is more lines of code, but it's clearer, and of course much more reliable.

Mark

kirkm · Mar 23, 2018

Nice example Mark. Wish i'd known this years ago.

MarkK · Mar 23, 2018

Good luck with the rest of the project.
Mark

Galaxiom · Mar 23, 2018

kirkm said:
Then CreateQueryDef("", sql). I see now the "" is Name meaning an existing query (and no sql part needed?)

The SQL parameter is provided by the sql string variable. It is optional during creation and can be added later by changing the SQL Property.

Not providing a name parameter means the query will not be appended to the QueryDefs Collection. A temporary query.

Saved Parameterised queries perform better than ad hoc queries built by concatenating a command string in VBA because their query plan is stored instead of being generated for each execution.

Parameterised query also work when creating a recordset which can't be done if the query refers to Access objects such as a form.

Can I escape both ' and " ? (1 Viewer)

MarkK

bit cruncher

kirkm

Registered User.

Mark_

Longboard on the internet

MarkK

bit cruncher

Mark_

Longboard on the internet

MarkK

bit cruncher

Mark_

Longboard on the internet

kirkm

Registered User.

MarkK

bit cruncher

kirkm

Registered User.

MarkK

bit cruncher

Galaxiom

Super Moderator

Similar threads

Users who are viewing this thread