sql character

  • Thread starter Thread starter Rizza
  • Start date Start date
R

Rizza

In VBA and Access I am using a textbox to enter criteria to search a field
using sql recordset.
How can I search for a percent symbol or a single quote in the field?
Thanks in advance
 
Real good question.

Looking for D'Amata

"D" & CHR(39) & "Amata"

Microsoft Help Examplses

Dim MyChar
MyChar = Chr(65) ' Returns A.
MyChar = Chr(97) ' Returns a.
MyChar = Chr(62) ' Returns >.
MyChar = Chr(37) ' Returns %.

List of CHR and values


CHR Actual
32
33 !
34 "
35 #
36 $
37 %
38 &
39 '
40 (
41 )
42 *
43 +
44 ,
45 -
46 .
47 /
48 0
49 1
50 2
51 3
52 4
53 5
54 6
55 7
56 8
57 9
58 :
59 ;
60 <
61 =
62 >
63 ?
64 @
65 A
66 B
67 C
68 D
69 E
70 F
71 G
72 H
73 I
74 J
75 K
76 L
77 M
78 N
79 O
80 P
81 Q
82 R
83 S
84 T
85 U
86 V
87 W
88 X
89 Y
90 Z
91 [
92 \
93 ]
94 ^
95 _
96 `
97 a
98 b
99 c
100 d
101 e
102 f
103 g
104 h
105 i
106 j
107 k
108 l
109 m
110 n
111 o
112 p
113 q
114 r
115 s
116 t
117 u
118 v
119 w
120 x
121 y
122 z
123 {
124 |
125 }
126 ~
127 
128 €
129 Â
130 ‚
131 Æ’
132 „
133 …
134 †
135 ‡
136 ˆ
137 ‰
138 Å 
139 ‹
140 Å’
141 Â
142 Ž
143 Â
144 Â
145 ‘
146 ’
147 “
148 â€
149 •
150 –
151 —
152 ˜
153 â„¢
154 Å¡
155 ›
156 Å“
157 Â
158 ž
159 Ÿ
160
161 ¡
162 ¢
163 £
164 ¤
165 ¥
166 ¦
167 §
168 ¨
169 ©
170 ª
171 «
172 ¬
173 ­
174 ®
175 ¯
176 °
177 ±
178 ²
179 ³
180 ´
181 µ
182 ¶
183 ·
184 ¸
185 ¹
186 º
187 »
188 ¼
189 ½
190 ¾
191 ¿
192 À
193 Ã
194 Â
195 Ã
196 Ä
197 Ã…
198 Æ
199 Ç
200 È
201 É
202 Ê
203 Ë
204 Ì
205 Ã
206 ÃŽ
207 Ã
208 Ã
209 Ñ
210 Ã’
211 Ó
212 Ô
213 Õ
214 Ö
215 ×
216 Ø
217 Ù
218 Ú
219 Û
220 Ü
221 Ã
222 Þ
223 ß
224 à
225 á
226 â
227 ã
228 ä
229 å
230 æ
231 ç
232 è
233 é
234 ê
235 ë
236 ì
237 í
238 î
239 ï
240 ð
241 ñ
242 ò
243 ó
244 ô
245 õ
246 ö
247 ÷
248 ø
249 ù
250 ú
251 û
252 ü
253 ý
254 þ
255 ÿ
 
Real good question.

Looking for D'Amata

"D" & CHR(39) & "Amata"

Microsoft Help Examplses

Dim MyChar
MyChar = Chr(65) ' Returns A.
MyChar = Chr(97) ' Returns a.
MyChar = Chr(62) ' Returns >.
MyChar = Chr(37) ' Returns %.

List of CHR and values


CHR Actual
32
33 !
34 "
35 #
36 $
37 %
38 &
39 '
40 (
41 )
42 *
43 +
44 ,
45 -
46 .
47 /
48 0
49 1
50 2
51 3
52 4
53 5
54 6
55 7
56 8
57 9
58 :
59 ;
60 <
61 =
62 >
63 ?
64 @
65 A
66 B
67 C
68 D
69 E
70 F
71 G
72 H
73 I
74 J
75 K
76 L
77 M
78 N
79 O
80 P
81 Q
82 R
83 S
84 T
85 U
86 V
87 W
88 X
89 Y
90 Z
91 [
92 \
93 ]
94 ^
95 _
96 `
97 a
98 b
99 c
100 d
101 e
102 f
103 g
104 h
105 i
106 j
107 k
108 l
109 m
110 n
111 o
112 p
113 q
114 r
115 s
116 t
117 u
118 v
119 w
120 x
121 y
122 z
123 {
124 |
125 }
126 ~
127 
128 €
129 Â
130 ‚
131 Æ’
132 „
133 …
134 †
135 ‡
136 ˆ
137 ‰
138 Å 
139 ‹
140 Å’
141 Â
142 Ž
143 Â
144 Â
145 ‘
146 ’
147 “
148 â€
149 •
150 –
151 —
152 ˜
153 â„¢
154 Å¡
155 ›
156 Å“
157 Â
158 ž
159 Ÿ
160
161 ¡
162 ¢
163 £
164 ¤
165 ¥
166 ¦
167 §
168 ¨
169 ©
170 ª
171 «
172 ¬
173 ­
174 ®
175 ¯
176 °
177 ±
178 ²
179 ³
180 ´
181 µ
182 ¶
183 ·
184 ¸
185 ¹
186 º
187 »
188 ¼
189 ½
190 ¾
191 ¿
192 À
193 Ã
194 Â
195 Ã
196 Ä
197 Ã…
198 Æ
199 Ç
200 È
201 É
202 Ê
203 Ë
204 Ì
205 Ã
206 ÃŽ
207 Ã
208 Ã
209 Ñ
210 Ã’
211 Ó
212 Ô
213 Õ
214 Ö
215 ×
216 Ø
217 Ù
218 Ú
219 Û
220 Ü
221 Ã
222 Þ
223 ß
224 à
225 á
226 â
227 ã
228 ä
229 å
230 æ
231 ç
232 è
233 é
234 ê
235 ë
236 ì
237 í
238 î
239 ï
240 ð
241 ñ
242 ò
243 ó
244 ô
245 õ
246 ö
247 ÷
248 ø
249 ù
250 ú
251 û
252 ü
253 ý
254 þ
255 ÿ
 
How can I search for a percent symbol or a single quote in the field?
Click to expand...

You can escape single characters as well as groups with square brackets:

WHERE IrishName LIKE 'O[']Hara%'
OR StringContainingPerCentChar LIKE '%[%]%'


Hope that helps


Tim F
 
I have tried to open the recordset using brackets(thanks Tim Ferguson) and
quotes in many sequences that seemed reasonable and some less. I seem to get
the same results or no value for given parameters. The Chr() is reacting the
same.

strSQL2 = "SELECT [" & strTbl & "].[" & strFld & "] FROM [" & strTbl & "]
WHERE " & _
"[" & strFld & "] Like '%' & '" & Chr(39) & "' & '%'"

syntax error in query expression [field_1] Like '%' & ''' & '%'
 
strSQL2 = "SELECT [" & strTbl & "].[" & strFld & "] FROM [" & strTbl & "]
WHERE " & _
"[" & strFld & "] Like '%' & '" & Chr(39) & "' & '%'"
Click to expand...


I am not quite sure what this Chr() bit is doing. I think the final command
should look something like this:

SELECT Eggs FROM Breakfast WHERE Eggs LIKE '%["]%'

Note that you don't need to keep referencing the table name since there is
only one table involved. And assuming that you have normal legal names, the
braces only serve to obfuscate too. Actually, since the double quote is not
special in T-SQL, you can dispense with those braces too:

SELECT Eggs FROM Chickens WHERE Eggs LIKE '%"%'

So, to get this from a VBA statement, you need something like

strSQL = "SELECT " & strFld & " FROM " & strTbl & _
" WHERE " & strFld & " LIKE '%""%'"

MsgBox strSQL


.... and please don't forget the second line!!

If you are looking for a single character, then using CHARINDEX makes the
thing much easier to read and might be quicker:

SELECT Eggs FROM Chickens
WHERE CHARINDEX(N'"', Eggs)>0

or
strSQL = "SELECT " & strFld & " FROM " & strTbl & _
" WHERE CHARINDEX(N'""', " & strFld & ") > 0"

MsgBox strSQL

Hope that helps

Tim F
 
When I open the recordset using Like '%[']%' the error reads: syntax error
in query expression. I am actually trying to search for a single quote not a
double, I can search for a double no problem. I think the problem exists
that the single quote is being read as the end of the string even though it
is in the middle of the braces or anything else I have tried. My original
plan was to be able to enter a character or set of characters into a textbox
and search a field returning all the results to a table. I am opening an ADO
recordset based on my SQL statement then inserting one line at a time the
recordset information. (if I insert all at once my process doesn't work for
date or number fields, a different issue though).
So far these suggestions are directed as I had hoped, however no success. I
looked in Access and AccessVBA help. Then google searched. Then used
CHARINDEX returning an undefined function error. It seems it is only for SQL
server or at least not for AccessVBA although I could be mistaken as I am a
rookie(obvious).

Here is the meat of my code.

Sub test()
Dim cnn As ADODB.Connection
Dim rstTbls As ADODB.Recordset, rstCurTbl As ADODB.Recordset
Dim strSQL As String, strSQL2 As String, strCombo As String, strBox As
String
Dim strTbl As String, strFld As String

strBox = [Forms]![Table Search]![SearchBox]
strCombo = [Forms]![Table Search]![ComboField]

Set cnn = CurrentProject.Connection
Set rstTbls = New ADODB.Recordset
Set rstCurTbl = New ADODB.Recordset

strSQL = "SELECT [Tbl Fields].* FROM [Tbl Fields] WHERE [Tbl Fields].

Like 'source_' & '%' AND [Field] like '" & strCombo & "' ORDER BY
"

rstTbls.Open strSQL, cnn, 0
Do Until rstTbls.EOF
strTbl = rstTbls!Table
strFld = rstTbls!Field
strSQL2 = "SELECT " & strFld & " FROM " & strTbl & " WHERE strFld & "
Like '%' & '" & strBox & "' & '%'"

rstCurTbl.Open strSQL2, cnn, 0
Do Until rstCurTbl.EOF
DoCmd.RunSQL "INSERT INTO [Tbl Search Report](
, [Field],
[Data]) SELECT '" & strTbl & "', '" & strFld & "', '" & rstCurTbl(0) & "'"

rstCurTbl.MoveNext
Loop
rstCurTbl.Close
rstTbls.MoveNext
Loop
Set rstCurTbl = Nothing
Set rstTbls = Nothing
Set cnn = Nothing
End Sub

If data in the field contains a single quote this is the error:
syntax error (missing operator) in query expression "da'ta"

I have fixed this by inserting all records at once without a second
recordset. The single quote search still eludes me.
Any more thought on this and/or any new suggestions would be greatly
appreciated.






Tim Ferguson said:
strSQL2 = "SELECT [" & strTbl & "].[" & strFld & "] FROM [" & strTbl & "]
WHERE " & _
"[" & strFld & "] Like '%' & '" & Chr(39) & "' & '%'"
Click to expand...


I am not quite sure what this Chr() bit is doing. I think the final command
should look something like this:

SELECT Eggs FROM Breakfast WHERE Eggs LIKE '%["]%'

Note that you don't need to keep referencing the table name since there is
only one table involved. And assuming that you have normal legal names, the
braces only serve to obfuscate too. Actually, since the double quote is not
special in T-SQL, you can dispense with those braces too:

SELECT Eggs FROM Chickens WHERE Eggs LIKE '%"%'

So, to get this from a VBA statement, you need something like

strSQL = "SELECT " & strFld & " FROM " & strTbl & _
" WHERE " & strFld & " LIKE '%""%'"

MsgBox strSQL


... and please don't forget the second line!!

If you are looking for a single character, then using CHARINDEX makes the
thing much easier to read and might be quicker:

SELECT Eggs FROM Chickens
WHERE CHARINDEX(N'"', Eggs)>0

or
strSQL = "SELECT " & strFld & " FROM " & strTbl & _
" WHERE CHARINDEX(N'""', " & strFld & ") > 0"

MsgBox strSQL

Hope that helps

Tim F
Click to expand...
 
instead of trying to put joker characters into sql try to put them all into
textbox.

if your texbox's text like *abc*12* then sql will select all records which
includes abc and 12 anywhere of record.

if you write abc* into textbox then sql will select all records which
starts with abc

using joker characters from textbox gives you more choices then write to
sql.

into sql;
select.........
where [field] like forms!form1.[textbox];

I think this will be more usefull.
 
When I open the recordset using Like '%[']%' the error reads: syntax
error in query expression. I am actually trying to search for a single
quote not a double,
Click to expand...


This works for me:
SELECT SerialNum, FName, LName
FROM dbo.Children
WHERE (LName LIKE N'%['']%')
My original plan was to be able to enter a character or
set of characters into a textbox and search a field returning all the
results to a table.
Click to expand...

strSQL = "... LIKE " & QuoteText(Me.txtTextbox.Value) & "..."

Public Function QuoteText(SomeText As Variant) As String
' use a variant in case it's passed a control.Value property
Const c_wSQ = 39 ' = asc("'")
Dim strTemp As String

If IsNull(SomeText) Then
strTemp = String$(2, c_wSQ)

Else
strTemp = Chr(c_wSQ) & _
Replace(SomeText, String$(1, c_wSQ), String$(2, c_wSQ)) & _
Chr$(c_wSQ)
End If
QuoteText = strTemp

End Function
I am opening an ADO recordset based on my SQL
statement then inserting one line at a time the recordset information.
(if I insert all at once my process doesn't work for date or number
fields, a different issue though).
Click to expand...

I have to say that I don't use ADO much... inserting one line of what at
a time into what?

B Wishes


Tim F
 
It works! I understand how the quotetext function works but I don't
understand why. It appears to me to pass four single quotes. How is the
recordset finding data containing one single quote?
I have to say that I don't use ADO much... inserting one line of what at
a time into what?
Click to expand...

I am inserting one line at a time the table name, field name, and data found
in the table.field based on the search text box.

WHERE (LName LIKE N'%['']%')
Click to expand...

I also don't understand what N is for. Is it a typo?


Tim Ferguson said:
When I open the recordset using Like '%[']%' the error reads: syntax
error in query expression. I am actually trying to search for a single
quote not a double,
Click to expand...


This works for me:
SELECT SerialNum, FName, LName
FROM dbo.Children
WHERE (LName LIKE N'%['']%')
My original plan was to be able to enter a character or
set of characters into a textbox and search a field returning all the
results to a table.
Click to expand...

strSQL = "... LIKE " & QuoteText(Me.txtTextbox.Value) & "..."

Public Function QuoteText(SomeText As Variant) As String
' use a variant in case it's passed a control.Value property
Const c_wSQ = 39 ' = asc("'")
Dim strTemp As String

If IsNull(SomeText) Then
strTemp = String$(2, c_wSQ)

Else
strTemp = Chr(c_wSQ) & _
Replace(SomeText, String$(1, c_wSQ), String$(2, c_wSQ)) & _
Chr$(c_wSQ)
End If
QuoteText = strTemp

End Function
I am opening an ADO recordset based on my SQL
statement then inserting one line at a time the recordset information.
(if I insert all at once my process doesn't work for date or number
fields, a different issue though).
Click to expand...

I have to say that I don't use ADO much... inserting one line of what at
a time into what?

B Wishes


Tim F
Click to expand...
 
I understand how the quotetext function works but I don't
understand why. It appears to me to pass four single quotes.
Click to expand...

No: it doubles them, that's all. By accident, I came across this
documented in the SQL Books On Line so I now know it's true rather than
assuming it. The example there is:

SELECT @au_name = 'O''Leary'

so there you are!!
WHERE (LName LIKE N'%['']%')
Click to expand...

I also don't understand what N is for. Is it a typo?
Click to expand...

(ahem) I don't either really. It's not a typo: the Access GUI sticks them
in front of all quoted strings so I tend to leave them there. My guess
it's something to do with forcing a Unicode string rather than ANSI, like
NCHAR() and NVARCHAR() and so on.

All the best


Tim F
 
Back
Top