A2000: searching for string beginning with

  • Thread starter Thread starter A man
  • Start date Start date
A

A man

I haven't used A2000 for a while. I searched the help for this but couldn't
find anything. I'm using a DAO recordset and the Filter property. How do I set
the filter property to find all records where [catnum] starts with HS?

I tried this but it doesn't work.
Set mydb = CurrentDb()
Set myset = mydb.OpenRecordset("MainQry", dbOpenDynaset)
myset.Filter = "[CatNum] like 'HS*'"
myset.MoveFirst
myset.MoveLast
cnt = myset.RecordCount
myset.MoveFirst

I'd rather not have to make separate queries for records that start with HS and
all other records. It just makes things messier.


Also, does VBA support regular expressions? I want to check if a variable
contains a number or not.

Thank you.


--
 
You might be better off doing:


Set mydb = CurrentDb()
Set myset = mydb.OpenRecordset("Select * from MainQry
Where [CatNum] like 'HS*'", dbOpenDynaset)
myset.MoveFirst
myset.MoveLast
cnt = myset.RecordCount
myset.MoveFirst

Depending on the size of the table, you might want to do:


Set mydb = CurrentDb()
Set myset = mydb.OpenRecordset("Select Count(CatNum) from
MainQry Where [CatNum] like 'HS*'", dbOpenDynaset)
cnt = myset(0)
Set myset = mydb.OpenRecordset("Select * from MainQry
Where [CatNum] like 'HS*'", dbOpenDynaset)

As with most things, YMMV


Chris Nebinger
 
Hi,

I don't really use the recordset filter feature (I
usually just use constructed sql statements), but I
believe that if you want to use it you have to assign the
filter value prior to opening the recordset.

Hope that helps.

-Ted Allen
 
From the help file (my emphasis) ...

<begin quote>
You can use the Filter property to restrict the records returned from an
existing object *when a new Recordset object is opened based on an existing
Recordset object*.

In many cases, it's faster to open a new Recordset object by using an SQL
statement that includes a WHERE clause.
<end quote>

In other words, in your example you'd need to do something like ...

Dim mySet2 As DAO.Recordset
....
Set mySet2 = mySet.OpenRecordset()

.... after applying the filter to mySet.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top