Can I escape both ' and " ? (1 Viewer)
- Thread starter kirkm
- Start date
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
..forever waiting... waiting for jellybean!
- Local time
- Tomorrow, 01:56
- Joined
- May 7, 2009
- Messages
- 19,242
Here is the link https://access-programmers.co.uk/forums/showthread.php?t=297170
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?
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
bit cruncher
- Local time
- Today, 10:56
- Joined
- Mar 17, 2004
- Messages
- 8,181
Kirk,
See what's happening there?
Mark
Code:
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 SubMark
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Tomorrow, 01:56
- Joined
- May 7, 2009
- Messages
- 19,242
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))
Dim rs as dao.recordset
Set rs= fnAnySql("Select * From tbl2000 where LC = @1 Order by tbl2000.Date", dat(0))
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.
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.
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.
Shall we continue or bale out. I don't know if this is getting anywhere.
MarkK
bit cruncher
- Local time
- Today, 10:56
- Joined
- Mar 17, 2004
- Messages
- 8,181
What doesn't work about what I posted?
Mark
MarkK
bit cruncher
- Local time
- Today, 10:56
- Joined
- Mar 17, 2004
- Messages
- 8,181
I'm not sure what it should do either. This is all you've given us to go on...
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
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.
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
bit cruncher
- Local time
- Today, 10:56
- Joined
- Mar 17, 2004
- Messages
- 8,181
You could re-write the code as a function...
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
Code:
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 SubMark
