Using the filter in a DAO recordset

  • Follow


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.

0
Reply Utf 11/26/2007 10:11:01 PM

Use single quote within the double quote

For example:

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

-- 
Good Luck
BS"D


"GLT" wrote:

> 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.
> 
0
Reply Utf 11/26/2007 10:44:04 PM

"GLT" <GLT@discussions.microsoft.com> wrote in message 
news:556071A4-E499-4E67-AF03-6E4B44CB6C1E@microsoft.com...
> 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.

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

(please reply to the newsgroup)

1
Reply Dirk 11/26/2007 11:57:09 PM

Hi Dirk,

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

Cheers,
GLT.

"Dirk Goldgar" wrote:

> "GLT" <GLT@discussions.microsoft.com> wrote in message 
> news:556071A4-E499-4E67-AF03-6E4B44CB6C1E@microsoft.com...
> > 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.
> 
> -- 
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
> 
> (please reply to the newsgroup)
> 
0
Reply Utf 11/28/2007 8:41:01 AM

3 Replies
1794 Views

(page loaded in 0.058 seconds)


Reply: