Using the filter in a DAO recordset

[GLT]

Hi,

I have a query that uses the following criteria:

Like "CS*" Or Like "HR*" Or Like "NR*" Or Like "NS*" Or Like "SR*" Or Like
"SS*" Or Like "WR*" Or Like "WS*"

What I would like to do is use the same criteria in a record set that has
been created using VBA.

Would this be done in the .filter parameter and if so how are the quotes
within quotes coded?

Any help is greatly appreciated.

Cheers,
GLT.

 

[Ofer Cohen]

Use single quote within the double quote

For example:

SQLStr = "Select * From TableName Where FieldName Like 'CS*' Or FieldName
Like 'HR*'"

 

[Dirk Goldgar]

Hi,

I have a query that uses the following criteria:

Like "CS*" Or Like "HR*" Or Like "NR*" Or Like "NS*" Or Like "SR*" Or Like
"SS*" Or Like "WR*" Or Like "WS*"

What I would like to do is use the same criteria in a record set that has
been created using VBA.

Would this be done in the .filter parameter and if so how are the quotes
within quotes coded?

I'm not sure what you mean. Do you mean ...

(A) You want to open a recordset using a dynamically-built SQL statement
that applies these criteria? In that case, you'd do something like this:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT * FROM MyTable WHERE " & _
"SomeField Like 'CS*' OR " & _
"SomeField Like 'HR*' OR " & _
"SomeField Like 'NR*' OR " & _
"SomeField Like 'NS*' OR " & _
"SomeField Like 'SR*' OR " & _
"SomeField Like 'SS*' OR " & _
"SomeField Like 'WR*' OR " & _
"SomeField Like 'WS*'"

or do you mean ...

(B) You already have an open recordset, and you want to open a filtered
recordset from it? In that case, you'd do something like this:

Dim rs As DAO.Recordset
Dim rsFiltered As DAO.Recordset

' ... code to open recordset rs goes here somewhere ...

' Open rsFiltered by applying a filter to rs.
rs.Filter = _
"SomeField Like 'CS*' OR " & _
"SomeField Like 'HR*' OR " & _
"SomeField Like 'NR*' OR " & _
"SomeField Like 'NS*' OR " & _
"SomeField Like 'SR*' OR " & _
"SomeField Like 'SS*' OR " & _
"SomeField Like 'WR*' OR " & _
"SomeField Like 'WS*'"

Set rsFiltered = rs.OpenRecordset

Or, of course, youy could mean something else entirely.

 

[GLT]

Hi Dirk,

Option (b) was what I was looking for but thanks to both of my responses
they provided much needed clarification.

Cheers,
GLT.