LIKE '%-%'

[George Hester]

This doesn't work in a DAO.Recordset. What's the trouble? It does work in a ADOR.Recordset but not in a DAO.Recordset.

strSQLHyphens = "SELECT [" & newTableName & "].[" & strFldName2 & "] " & _
"FROM [" & newTableName & "] " & _
"WHERE ([" & newTableName & "].[" & strFldName2 & "] " & _
"LIKE '%-%');"

fails with

Set RsQry = dB.OpenRecordset(strSQLHyphens)
'RsQry.MoveLast
MsgBox RsQry.RecordCount
Set RsQry = Nothing

But this:

strSQLHyphens = "SELECT [" & newTableName & "].[" & strFldName2 & "] " & _
"FROM [" & newTableName & "] " & _
"WHERE ([" & newTableName & "].[" & strFldName2 & "]='www.microsoft.com'";"

works just fine. It is not an issue of defining variables or whether they exist. That is not the problem. It seems the LIKE predicate is not supported here. This is a local database. Is that right?

 

[George Hester]

Excellent. And I didn't even have to use .MoveLast. Mmmm.... I was expecting 1 for the Recordcount without the .MoveLast. Welp we'll see if this can help my sorting issue.

--
George Hester
__________________________________

This doesn't work in a DAO.Recordset. What's the trouble? It does
work in a ADOR.Recordset but not in a DAO.Recordset.

strSQLHyphens = "SELECT [" & newTableName & "].[" & strFldName2 &
"] " & _
"FROM [" & newTableName & "] " & _
"WHERE ([" & newTableName & "].[" & strFldName2 &
"] " & _
"LIKE '%-%');"

fails with

Set RsQry = dB.OpenRecordset(strSQLHyphens)
'RsQry.MoveLast
MsgBox RsQry.RecordCount
Set RsQry = Nothing

But this:

strSQLHyphens = "SELECT [" & newTableName & "].[" & strFldName2 &
"] " & _
"FROM [" & newTableName & "] " & _
"WHERE ([" & newTableName & "].[" & strFldName2 &
"]='www.microsoft.com'";"

works just fine. It is not an issue of defining variables or whether
they exist. That is not the problem. It seems the LIKE predicate is
not supported here. This is a local database. Is that right?

No, that's not right. DAO and ADO use different wildcard characters.
See the help file on "Like Operator", and follow up with the help file
entry on "wildcard characters", where it says:

<quote>
The asterisk (*), percent sign (%), question mark (?), underscore
character (_), number sign (#), exclamation point (!), hyphen (-), and
brackets ([ ]) are wildcard characters. You can use these characters in
queries and expressions to include all records, file names, or other
items that begin with specific characters or match a certain pattern.
You can also use wildcard characters and matching characters to further
refine a search when using an SQL statement.

*Note* The percent sign (%) and underscore character (_) wildcards are
only used when using the Microsoft® OLE DB Provider for Jet. They are
not exposed through DAO or the Microsoft Access UI. They will be treated
as literal values in DAO or the Access UI.
</quote>

Don't overlook that ending note. In your case, you should use

LIKE '*-*'

to get the matches you want.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)