wildcard char

[Rizza]

I am using Access 2k. I am trying to search for a % in a field.
This is my code that is not working:

Dim strTbl As String, strFld As String
strTbl = "Table_1"
strFld = "model_a"
DoCmd.RunSQL "INSERT INTO [Tbl Search Report](

, [Field], [Data])
SELECT '" & _
strTbl & "', '" & strFld & "', [" & strTbl & "].[" &
strFld & "]" & _
" FROM [" & strTbl & "] WHERE [" & strFld & "] Like
'%[%]%'"

I have also looked in sql books online and found:
Like '%#%%' Escape '#'

This does not work either.
Any suggestions are appreciated.

 

[Guest]

Try Like *%*

 

[Tim Ferguson]

I am using Access 2k. I am trying to search for a % in a field.
This is my code that is not working:

You don't say what is not working. Is there a syntax error, or is the
thing just not finding the correct records, or is it returning all the
records?

If you are using ADO, then this definitely should work:
WHERE MyField LIKE '%[%]%'
in other words, use a [%] to look for the required character

Or you can use
WHERE CHARINDEX('%', MyField) > 0
because that is not a regular expression and therefore the percent is not
magic.

If you are using DAO, then % is not a wild card anyway, so you would
need:
WHERE MyField LIKE "*%*"
or
WHERE INSTR(MyField, "%") > 0

The trouble is that using DoCmd.RunSQL you get very little control over
what happens: Access's default has switched from DAO to ADO and back to
DAO again in recent versions. What runs now may break again in future
versions. Better by far to use a proper VBA vis

' DAO
Set db = CurrentDB()
db.Execute jetInsert, dbFailOnError

' ADO - check this, as I'm not a ADO wizard
Set cmd = New Command
With cmd
Set .ActiveConnection = CurrentProject().Connection
.Type = adCmdText
.ReturnsRecords = False
.SQL = adoInsert
.Execute

End With



and so on. You only get to choose where you are going if you learn to
drive first..!


All the best


Tim F