Can I escape both ' and " ?

[kirkm]

Trying to build a select query where criteria may contain ' and " chars.
What is a good solution ? Thanks.

 

[MarkK]

Use a parameterized QueryDef.
Cheers,
Mark

 

[kirkm]

Thanks Mark, but it's not so easy when you don't know what a parameterized QueryDef is !
It's good to know there's a solution. Could you show an example please?

 

[MarkK]

How about you describe your problem. Then I'll describe a very simple and robust solution, to that problem.
hth
Mark

 

[kirkm]

OK, thanks.. I have "Select * From tbl2000 where LC = '" & dat(0) & "' Order by tbl2000.Date"

Dat(0) and Col LC may have the ' and " chars and thus give run time error 3075.

 

[arnelgp]

Try using AnySql() search it on the forum.

 

[kirkm]

I find my own question and something about Truncating tables. I searched Google too but no working solution. Maybe you have a link to AnySql() ? I don't want to spend too much time on this as lots to do (once this is sorted).

 

[arnelgp]

Here is the link https://access-programmers.co.uk/forums/showthread.php?t=297170

 

[kirkm]

Sorry arnelgp, that's not something I can follow. Would have tried it but I could not work out what ParamArray p() as Variant should be.
I have succeeded though by If Then looking at whether my criteria contains ' or " and using the sql that works.
This query def parameter is that a parameter the user inputs manually by prompt?

 

[MarkK]

Kirk,

Sub Teste012374901247()
    Const SQL As String = _
        "SELECT t.* " & _
        "FROM tbl2000 As t " & _
        "WHERE t.LC = p0 " & _
        "ORDER BY t.Date"
    
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("p0") = dat(0)
        With .OpenRecordset
[COLOR="Green"]            'do something with the recordset[/COLOR]
            .Close
        End With
        .Close
    End With
End Sub

See what's happening there?
Mark

 

[arnelgp]

If paramarray is new to you google it. You cant prigram with just a basic tools in your hand.

Dim rs as dao.recordset
Set rs= fnAnySql("Select * From tbl2000 where LC = @1 Order by tbl2000.Date", dat(0))

 

[kirkm]

No, Mark but it's very intriguing ! What is p0 ?
The sql prints as SELECT t.* FROM tbl2000 As t WHERE t.LC = p0 ORDER BY t.Date

Running it shows Dat(0) as not defined, so I passed in a value for Dat() and got a record count of 1 (which is right). There's no recordset name so I couldn't figure out how to see the field contents.

 

[MarkK]

I got dat(0) from you in post #5. This is why I say describe your problem completely. If you only describe your problem partially, I can only give you a partial solution.
Mark

 

[kirkm]

I can't think how to describe the problem any better. Dat(0) is theWhere criteria and shouldn't matter what it is, well from my point of view !
Shall we continue or bale out. I don't know if this is getting anywhere.

 

[MarkK]

Shall we continue or bale out. I don't know if this is getting anywhere.

What doesn't work about what I posted?
Mark

 

[kirkm]

Mark, I'm not sure what it should do. My results in Msg 12.

 

[MarkK]

I'm not sure what it should do.

I'm not sure what it should do either. This is all you've given us to go on...

"Select * From tbl2000 where LC = '" & dat(0) & "' Order by tbl2000.Date"

Presumably you want to be able to replace dat(0) with text that may contain delimiters, and I have shown how I would do that.
I don't know what else you need it to do.
Mark

 

[kirkm]

Mark, you guys know much more than me, and I assumed that bit of sql would be self-describing. I want a recordset with all fields from tbl2000 where (field) LC = (variable) Dat(0) and Ordered by (field) Date.
My question was about Dat(0) where it contains ' & " chars that cause errors and how to escape, or otherwise, get around that. You said to use a parametized query def, but I haven't been able to do that yet. And I've tried but not had any success with the code in an earlier message.

 

[MarkK]

You could re-write the code as a function...

Function GetRecordset(LC As <whattypeisLC?>) As DAO.Recordset
    Const SQL As String = _
        "SELECT t.* " & _
        "FROM tbl2000 As t " & _
        "WHERE t.LC = p0 " & _
        "ORDER BY t.Date"
    
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("p0") = LC
        Set GetRecordset = .OpenRecordset
        .Close
    End With
End Sub

So you pass in the value of LC (not sure what type it is) and then it returns the recordset. Does that make sense? Again, if you say more about what you need to do with the recordset or where the value LC comes from, I could do more about hooking this up to your actual problem, but maybe this helps,
Mark

 

[Mark_]

@MarkK,

As I understand, Kirk is using a variable to hold the contents of a where clause. As such Dat(0) should have a value of

MyDateField <= "#4/5/2018#" AND MyOtherField = "MyValueHere"

Kirk, please let me know if this is correct.